Bulk change URL paths using SQL

I’ve recently done a big clean up of my sites, moving my main site from Drupal to WordPress, and shuffling around the positions of my install folders. So far, so good. But I found a problem: media uploaded before broke since the file paths had changed. In the past I’d fixed it manually, but this time I’d had enough and decided to do it via SQL. Here’s how you do it.

1. Go to PHPMyAdmin

If you’d done an autointall of your site CMS, you may not have looked at the database as of yet. You can find which one it is by using your settings.php (Drupal) or wp-config.php (WordPress) file. \Then you can find PHPMyAdmin from CPanel or whatever your hosting uses.

2. BACK UP YOUR DATABASE.

This is going to be hardcore. And if you do something wrong, you’re screwed. Back that database up.

3. Run the SQL.

If you’re on Drupal, you’ll use something like this:

# for DRUPAL
UPDATE `field_data_body` SET `body_value` = replace(`body_value`, "sites/vickyteinaki.com", "sites/2013.vickyteinaki.com");


For WordPress, it’s a little different.

# for WP
UPDATE `wp_posts` SET `post_content` = replace(`post_content`, "http://blog.vickyteinaki.com/uploads/", "http://vickyteinaki.com/content/images/wordpress/");


But what if you need to do more careful selection changing, e.g. if the site has long URLs in it? As it turns out, SQL in this case is like PHP in that you can use either single or double quotes in your queries as long as they don’t show up in the main copy in the same format.


UPDATE `wp_posts` SET `post_content` = replace(`post_content`, 'src="/uploads/', 'src="http://vickyteinaki.com/content/images/wordpress/');

For me this has saved a lot of manual changes. Hopefully it helps. (Of course, you could always download the database and change the text using a text editor and GREP, but this is faster).