Search and Replace for WordPress Databases

WordPress makes, in my opinion, the sin of storing serialized PHP data in the database. In my view using databases to store non-platform-agnostic information is always dangerous, but then I spent half my career working on cross-platform code and databases, so perhaps it’s just me.

The code in action.Anyway, this always presented a problem for those of us migrating WordPress databases or even, just needing to do a quick search and replace across the entire database. For example, you might have moved office and wish to change all instances of your phone number on a website. It could be mentioned in a lot of posts, and in plugin settings.

And that’s why we wrote this little utility. In this incarnation if you drop it into your root folder it will pick up the WordPress DB connection settings, but if you wish to play safe and run the code elsewhere you’ll have to go into the code and set these yourself.

Ultimately you do have to be careful. This code can seriously damage the health of your database. It will cheerfully spend just a few seconds reducing your settings to rubble. So make sure you BACKUP FIRST!

To see how you can use this tool to aid migrations, check out the Interconnect IT article on WordPress migrations.

If you are in any doubt whatsoever about how to use this plugin, then please consider getting an expert in. You could even contact us over at Interconnect IT.

Download

Categorised as: Plugins

6 Comments

Add your own comment
  1. December 5, 2009 2:37 pm, link

    [...] a good chance that careless use could lead to a lot of damage. Treat it with respect and care! Read more and download the WordPress Database Search and Replace Tool. Interconnect IT WordPress Developers | Spectacula GPL Themes Club | [...]

  2. May 10, 2010 10:55 am, link

    Hi Dave

    Really keen to use this script. I have copied it to the root folder (httpdocs) but when I run it I still only get the Wordpress Fatal Error screen - no sign of your script.

    Am I missing something?

    Cheers

    Sam

  3. June 11, 2010 8:18 am, link

    This was a night saver!
    unfortunatly i had to spend a couple of hours figuring out what was wrong..
    ironically, a script that was supposed to avoid data corruption was corrupting data,

    if you are running this script as a stand alone (as i was running version 1.0 http://www.davesgonemental.com/mysql-database-search-replace-with-serialized-php/ )
    you will need to add
    mysql_set_charset(’utf8′);
    bellow mysql_connect()
    otherwise you will get wrong lenghts in the serialized data.

    another issue has to do with Error Notices.
    there will be so many that you script will die beofre it finishes if your time limit is 30 secs.
    those notices some from using unserialize() with no serialized data.
    to avoid that use this conditional

    $unserialized = @unserialize( $data_to_fix );
    if ( ‘b:0;’ === $data_to_fix || false !== $unserialized ) {

    one last thing,
    this script wont run if its not installed in the root wordpress folder,
    because get_bloginfo() wont be a registered function.
    to solve that,,

    use this

    <input id=”search_text” type=”text” name=”search” value=”" class=”text”/>

    Thanks a lot for your work.
    just a little back in return
    - wjm

  4. July 12, 2010 6:33 am, link

    This script, in it’s previous versions, has been a life save for me. However, with this version, I notice that it is not actually replacing all the instances of the string within the WP database.

    For example, the following tables:

    - wp_options
    - wp_rg_form_meta
    - wp_rg_lead_detail_long

    still have the old values after running the script. I’m using WordPress 3.0 with mySQL 5.1.41

  5. July 12, 2010 6:51 am, link

    OK, after looking into it further, the script is working properly - it was my error.

  6. August 6, 2010 12:25 am, link

    Very useful Dave - Thanks

    I have just moved a local installation of WP to a webhost & made good use of this.

    PS - it was good to meet you at WordCampUK 2010

Please leave a comment