MySQL Data Migration: Converting Data Imported from External Forums into Drupal

Recently, I had to import two old forums into Drupal. Of course, this means you now have a lot of data that may or may not follow the standards of your new system. In the case of this import, one of the forums had its own tagging system for bold and underlining of data ([B] and [U]). The thing about content with Drupal is that everything is stored in a MySQL database. The issue I was faced with is once a forum post is opened, the content would display the [B] and [U], which means absolutely nothing to Drupal, unless you wrote a module that would make sense of it.

There are several ways you could go about this issue, but the simplest way is to deal with the data right in the database rather than scripting some code that searches and replaces content before import. To tackle the problem, we can use some SQL functions that will search for a given string and convert it right within the database.

In the Drupal database, there are two tables where this data inconsistency would exist – Node Revisions and Comments. I’ve separated out the commands so you understand what I’m doing.

# Update tags in body in node_revisions table
UPDATE node_revisions SET body = REPLACE(body,'[B]','<strong>');
UPDATE node_revisions SET body = REPLACE(body,'[/B]','</strong>');
UPDATE node_revisions SET body = REPLACE(body,'[U]','<u>');
UPDATE node_revisions SET body = REPLACE(body,'[/U]','</u>');

# Update tags in teaser in node_revisions table
UPDATE node_revisions SET teaser = REPLACE(teaser,'[B]','<strong>');
UPDATE node_revisions SET teaser = REPLACE(teaser,'[/B]','</strong>');
UPDATE node_revisions SET teaser = REPLACE(teaser,'[U]','<u>');
UPDATE node_revisions SET teaser = REPLACE(teaser,'[/U]','</u>');

# Update tags in comment in comments table
UPDATE comments SET comment = REPLACE(comment,'[B]','<strong>');
UPDATE comments SET comment = REPLACE(comment,'[/B]','</strong>');
UPDATE comments SET comment = REPLACE(comment,'[U]','<u>');
UPDATE comments SET comment = REPLACE(comment,'[/U]','</u>');

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>