drupal-6-database-notes-content-in-node-revisions
If you ever need to access the "raw content" in an "emergency" look for the node & node_revisions table in your raw sql dump (yes, you do run backups, don't you!)
Or to import a large amount of content make a fake sql database file to import?
You need: phpMyAdmin, notepad2 (great .sql syntax highlight)
(sample export of .sql)
INSERT INTO node_revisions
VALUES (116, 116, 1, 'Title of post', 'Body', 'Teaser', '', 1162300341, 3);
If you have a test database create a single content entry (e.g. blog) and then use:
phpMyadmin to drill down into the database until you find the "node" table...
Structure tab will show you what the "columns" are, Export into a .sql file and use notepad2 to see the exact structure
--
-- Dumping data for table node
--
INSERT INTO node
(nid
, vid
, type
, language
, title
, uid
, status
, created
, changed
, comment
, promote
, moderate
, sticky
, tnid
, translate
) VALUES
(1, 1, 'blog', '', 'testtitle', 1, 1, 1284408902, 1284408902, 2, 1, 0, 0, 0, 0);
Repeat the above for the "node_revisions" table except that this time:
For fun try modifying the text by hand, saving the file, and then use myPhpAdmin to import it again, e.g. look how I changed the "teaser" field to be have "hack" ...
INSERT INTO node_revisions
(nid
, vid
, uid
, title
, body
, teaser
, log
, timestamp
, format
) VALUES
(1, 1, 1, 'testtitle', 'testbody', 'testbodyhackteaser', '', 1284408902, 1);
Now for the scary part, assuming that the above INSERT INTO list of columns matches you can put almost any data into the revisions .sql file (as long as the node .sql file matches)... unless it doesn't work and then you'll have to figure it out or do it manually?
(notice that the raw dumps of tables from phpMyAdmin don't include the pretty commands like "locking" and unlocking the tables...)
so to import a raw dump from my other website I had to delete the 1 row (nid=1) from the test site (so there aren't duplicate nid=1)
url_alias
Don't forget the url's! in table url_alias, same procedure as above
-
get your raw full .sql file of your database
-
search it for -- Dumping data for table
url_alias
-
select copy all of that text until the end (usually ALTER TABLE
url_alias
ENABLE KEYS */; UNLOCK TABLES; -
use phpMyAdmin to dump the test/fresh database url_alias table
-
compare the table structures... very exciting
-
replace the test url_alias.sql file content from INSERT INTO
url_alias
(pid
,src
,dst
,language
) VALUES (1,... with your desired content (hopefully properly exported/formatted from step 3)
remove any pesky lock/unlock stuff that might not be in the really raw phpMyAdmin dump
-
delete the "rows" (aka data) in the url_alias table = test database using phpMyAdmin
-
save the "hacked" file and upload it using phpMyAdmin
-
look at your test site and go "wow"
to learn more read this guy's advice http://highervisibilitywebsites.com/import-raw-content-nodes-users-drupal