Upgrade Your Drupal Skills

We trained 1,000+ Drupal Developers over the last decade.

See Advanced Courses NAH, I know Enough

Drupal 6 Bulk Update Node Body Input Format

Parent Feed: 

I ran into a situation in which I created a new Input Format in Drupal 6 on a site that had a lot of content. This input format was similar to the default Filtered HTML input format, however I wanted it to have a few different options.

I wanted all anonymous users to only be able to access the Filtered HTML input format, but authenticated users (or users with specific roles) to be able to access this new input format. This part was easy with Drupal's administration interface the problem came when I wanted to change the input format for the body field for all the posts on this site to the new input format.

I really did not want to go into each post one by one, scroll down to the body field, and change the input format, so I came up with a quick database solution using MySQL.

The node body field data is stored in the node_revisions table in the MySQL database. In this table there is a column called "format" that contains the id of the input format that is being used on that post. I only wanted to change this for a few content types, not every content type on the site, I also only wanted to change it for content that was published (leaving the unpublished content alone).

The first step was to look up the input format in the filter_formats table to get the correct new format id to use. Depending on the type of database viewer you are using (PHPMyAdmin, MySQL Workbench, Command Line MySQL, etc) this might be slightly different. You can probably click on the database table to see the records or you can run

SELECT * FROM filter_formats;

This will give you something like:

In my case the format ID I needed was 4. Now I needed to construct the MySQL UPDATE query to go into the node_revisions table and update all the posts that were of the specific content types I needed that were also published. I also only wanted to change the ones that were set to Filtered HTML (leaving any other formats unchanged).

The query I constructed looked like:

UPDATE node_revisions AS nr
INNER JOIN node AS n ON nr.nid = n.nid
SET nr.format = 4
WHERE n.status = 1 AND n.type IN ('blog', 'content_2') AND nr.format = 1

Note: Be careful with this. Anytime you are editing the database manually you risk messing things up. I would suggest making a backup prior to doing this if you are unsure of what you are doing. Also, if you are using revisioning (which I was not in this example), this will cause some inconsistencies as this change should probably be registered as a new revision. In this case you might want to look into writing a Drupal script in PHP to load the nodes in question, change the status, and then execute a node_save. The MySQL route just happened to be easier for me.

Author: 
Original Post: 

About Drupal Sun

Drupal Sun is an Evolving Web project. It allows you to:

  • Do full-text search on all the articles in Drupal Planet (thanks to Apache Solr)
  • Facet based on tags, author, or feed
  • Flip through articles quickly (with j/k or arrow keys) to find what you're interested in
  • View the entire article text inline, or in the context of the site where it was created

See the blog post at Evolving Web

Evolving Web