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;
This works well, but, if
This works well, but, if you're using Pathauto, all your path aliases remain in the database, so if you're doing this to import fresh content (using Node Import), then you get aliases with numbers appended to them.
Rather use something like this in a page node:
<?php
$node_type = 'company';
$aquery = db_query("SELECT n.nid FROM {node} n WHERE n.type = '%s'", $node_type);
if (db_num_rows($aquery) && user_access('administer nodes')) {
while ($n = db_fetch_object($aquery)) {
set_time_limit(5);
node_delete($n->nid);
}
echo 'You can proceed directly to the ' . l('import node wizard','admin/node/node_import') . ' to import new events.';
}
else {
echo "No $node_type found or you do not have permission to modify nodes.";
}
?>
The above code comes from http://drupal.org/node/92861#comment-238000, with a slight modification or two.
______________________________________________________________________________________________________
mybesinformatik.com - Drupal website development
Do not delete nodes in the database
Deleting nodes at the database level is a bad idea because there are so many tables in the database that contain node-related data. If deleting nodes in bulk at admin/content/node is not adequate for your needs then you need to make use of the node delete api function.
Views Bulk Operations
Another way is to install Views and Views Bulk Operations. You can do alot of bulk/mass actions this way (including delete) through the api.
What about automation?
I'm writing shell scripts to pull a drupal db from production servers to dev servers, and making a local copy of a large production site is a huge waste of space. I'd like to keep all of the prod site's modules, content types, theming, etc, and just remove the content - starting with a customized clean install, basically. Is there a list of every table I should empty to get rid of all the site's content but keep everything else?
I don't think there's a list
I don't think there's a list because it depends on what you have for content types... but sounds like you could just use Views Bulk Ops and a good View to filter down to all node types you want to get rid of, then run a bulk delete.
You'd need another View for users, but same process to get rid of unwanted accounts.
So you could pretty much empty your db w/two Bulk Op Views.
(You would not be able to get rid of menus this way, though Pages and whatever other node types you've enabled menus for.. those specific menuitems would go away I'm pretty sure)
This is very close to what I'll be doing in a week or so when our dev site goes live, except we're going to keep all the Page node types (and a few other things like Ads). Everything else goes, then we import some old stuff.