How to use phpmyadmin and sql to perform mass operations on nodes
Many hosts offer a GUI (graphical user interface)/web based administration system for managing mysql databases. phpmyadmin is an example of such. Running sql commands from phpmyadmin is very easy and can save a lot of time with basic admin tasks.
eg. You've added 30 book pages to the system and forgot to turn off publish to front page and a pager appears on your home page below a listing of x number of the recently added book pages.
Without having to change any site templating or edit each one of those 30 book pages, you can easily update the promote table for the book node types.
Here's how to...
1) Login to phpmyadmin and click on the node table.
Usually when you login to phpmyadmin you will see a list of your databases or a link to your database file. Navigate/click to the database you wish to edit. phpmyadmin will then display a list of tables in this database in the left hand navigation frame as well as on the main content frame. Using the left hand navigation frame, scroll down to the listing called node. This is your node table and contains data related to all node content in your Drupal site. This includes story, book, page and custom node types.
Once you have clicked the node table, you will a list of the columns and their corresponding field names. eg. nid, vid, type etc.
2) Click on the sql tab and run your sql statement
Right on top of your main screen you should see a tabs called Browse, Structure, SQL etc. Click the SQL tab.
You should see this: SELECT * FROM `node` WHERE 1, which is the generic query run when you click the Browse tab. Its a query that lists all rows in the node table.
Lets run a simple query to update all our book type nodes to not appear on the front page....
Precaution: You should backup your database before running any queries especially if you are new to this. Running an incorrect query can potentially damage your entire db.
Copy and paste the following code into your sql box and click go.
UPDATE `node` SET `promote` = '0' WHERE `type` = 'book' AND `promote` = '1'
The sql query will change the promote setting of all book nodes that are promoted to the front page.
The other way around
UPDATE `node` SET `promote` = '1' WHERE `type` = 'book' AND `promote` = '0'
This query will promote all book nodes to the front page
Maybe you want to enable commenting for all book pages
UPDATE `node` SET `comment` = '2' WHERE `type` = 'book'
Here's a resource to learn more about sql queries http://sqlzoo.net/
bulk delete
I needed to delete 1000s of nodes of a specific content type that I'd added via node_import for a test. doing this via /admin/content/node was painful.
drupal=# delete from node_revisions where nid in (select nid from node where type='almanac_data');DELETE 7435
drupal=# delete from node_counter where nid in (select nid from node where type='almanac_data');
DELETE 7435
drupal=# delete from node where type='almanac_data';
DELETE 7435
drupal=# truncate content_type_almanac_data;
TRUNCATE TABLE
note, I'm using postgres, the same should apply to mysql, at least in theory... after dropping this many tuples, a vacuum and reindex was in order, this is fairly postgres specific.
all caveats about backing up stuff apply in triplicate. this didn't remove the content type, it just cleaned off the nodes I'd created which used that type.
I've successfully done this
I've successfully done this in MySQL. For content type 'names_or' the commands were as follows - and they must go in this order!
delete from node_revisions where nid in (select nid from node where type='names_or');
delete from node_counter where nid in (select nid from node where type='names_or');
delete from node where type='names_or';
truncate content_type_names_or;