I had a problem recently where I had spent ages building a new CMS driven site which had loads of content – all interlinking with keyword rich anchor text etc, a WordPress blog, an email marketing system built in and a few other gismos to boot. With everything just about finished, the site SEO’d (well on-page SEO anyway) and uploaded to the final server for final tests I hear the imortal phrase “We’ve decided to change the site name and domain”.

I suppose you can guess my thoughts – 1000’s of database records across over 60+ different tables – any of them containing a reference to the name or the domain name. Oh please oh please let there be an easy way to change all these!!

In come comes the Google searches:

“replace text in database”
“replace text in whole database”
“find and replace text mysql”
etc etc

The results were coming in thick and fast but most of them just seemed to deal with individual columns or tables or you have to export all your data to text files and use find and replace functions in things like notepad – oh and of course you get the REALLY unhelpful people that just tell you how you should have done it in the first place. None of them really did what I wanted (although the export to file would have but I wanted another way!).

So – as usual I was left to figure it out myself. I thought there must be some kind person that has written a PHP script which you can put in search text and a replacement, point it to a database and it just loops through all the available tables, columns and records – then outputs a nice report to say where it had made the updates.

Could I find it? ….. nope.

So I wrote it myself. If you want it then here it is: http://mj7.co.uk/am03

Please let me know if you find it useful or if you can make it better I’d love to have it streamlined – tweet me @mjdigital or comment below.

UPDATE: Whilst this script still works and can be of use I would recommend using the script created by Interconnect/IT as it has a great user interface and handles serialized data. You can see/download the Interconnect/IT script here: https://interconnectit.com/products/search-and-replace-for-wordpress-databases/



  1. Eric Amundson
    Oct 01, 2009

    Hi Mark,

    This is exactly what I was looking for!

    I’ve been a WordPress dev for some time and there are occasions where I have to move a database that’s been customer-maintained and a simple search and replace in gedit or another text editor just won’t do the trick.

    Your script worked on the first go. 🙂

    When I have some time, I’ll dig into it and see if I have any questions or can make suggestions.

    Cheers and thanks again!

  2. Jay
    Oct 07, 2009

    Mark : YOU are a legend 🙂

    Thank you so much. This is great script and I it is my lucky day that I finally found it after hours of searching. It would be great if can have wildcard option too. I just tested it on my local sever and it is a charm.

    Thank you sooooo much.

  3. mj7
    Oct 07, 2009

    Thanks for the comments – wildcards would be great but at the moment MySQL does not support REGEXPs within the REPLACE function which would be the nicest way to do it.

    I have tweaked the file a little on line 49 to allow for uppercase letters in the DB name – it is now

    $tables[] = $tables_r['Tables_in_'.strtolower($database)];


  4. mj7
    Oct 20, 2009

    For anyone interested w8.se have updated this code allowing you to replace more than one value at a time – see: http://www.w8.se/2009/09/30/database-multiple-search-replace/

    Keep your mods coming!

    If you let me know then I will link to it here (not that w8.se did!)

  5. Dustin Dempsey
    Nov 07, 2009

    Doesn’t seem to be working for me. I get a bunch of errors:

    example error #1 (repeated a number of times):
    Notice: Undefined index: Tables_in_database_db in path/to/file/mysql-search-replace.php on line 49

    example error #2 (repeated a number of times):
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in path/to/file/mysql-search-replace.php on line 68

    Any idea how to fix this?
    Would love to be able to use this script.


    • mj7
      Nov 07, 2009

      Not wanting to sound obvious but have you made sure you put in your database name and connection details correctly?

      If yes then can you let me know the name of your database – I am aware that the Tables_in…database… command is a little tetchy when it comes to case sensitivity and special characters – if you want to PM me then please do (see contact page) – or you can write here.

      • Dustin Dempsey
        Nov 09, 2009

        I used the code found at w8.se and it works for me.
        Not sure what the issue was on the original script.

        Thanks for the follow up and the email message.
        It’s much appreciated.

  6. […] searched around and found this PHP script by Mark Jackson which makes it much faster and easier to search and replace data across an entire MySQL […]

  7. Usacttatduh
    Jan 02, 2010

    Which WP theme are you using?

  8. Stainless
    Jan 20, 2010

    Hi MJ7, Quick THANKS!!! I went through the same avenues of Google, Bing, etc and everyone had a single field replacement script but nothing for the whole data base… very frustrating. But you saved the day as my Joomla site backend was used on a new site, so a full search replace had to occur and it worked like a charm. Here are some of the keywords that I searched for; phpmyadmin search and replace, mysql replace all, phpmyadmin find replace complete, phpmyadmin search/replace database, mysql search replace whole database I hope that these get you higher in the search engines. Again thanks! Stainless

  9. Bluesplayer
    Jan 26, 2010

    Very sweet. Using it on my software store now and works a treat.


  10. Toby Adams
    Apr 22, 2010

    Great stuff, thanks for this!

  11. Stephen R
    May 17, 2010

    Thank you for this! Just great.

    Yeah… I too came across the people with the “Instead I'll tell you how you should have set up your database.”

    Reminds me of the scene in Funny Farm:

    “Hey Mac, how do you get to Redbud?”

    “If I wanted to go to Redbud, I wouldn't start from here.”

  12. Stephen R
    May 17, 2010

    Question: Does this handle serialised array data or will that cause problems?

  13. mj7
    May 17, 2010

    Hey, thanks for your comments. I'm afraid at the moment this script does not handle serialized arrays. I am currently working on a new version which does and also allows regular expressions for your search and replacements – I'll keep you posted once I've finished it.

  14. Devin Walker
    Aug 11, 2010

    Nice code, it could be so much more… maybe a WordPress plugin?

  15. Nikko
    Sep 03, 2010

    Awesome! Just what I needed

  16. Kosch
    Sep 23, 2010

    Thank you very much. Just like you I've been moving alot of databases from dev to production and production back to dev to refresh the content and trying to sort out all the find and replaces for the old URL's was a pain. This saves me so much time thank you!

  17. Rose
    Nov 03, 2010

    Great Convenient Tool!!!! This is what i needed too and it worked great. Just felt like leaving a note saying Thanks!

  18. mj7
    Nov 03, 2010

    You're most welcome. Glad it worked for you.

  19. Keith Fawcett
    Dec 20, 2010

    Fantastic tool! Thanks a lot.

  20. dandy
    Jan 30, 2011

    Thanks alot for this wonderful script. I've looking for it.

  21. walmley website design
    Feb 01, 2011

    I recently wanted to replace a string within MySQL on the fly, but the field could contain 2 items. So I wrapped a REPLACE() within a REPLACE(), such as:

    REPLACE(REPLACE(field_name, “what we are looking for”, “replace first instance”), “something else we are looking for”, “replace second instance”)

    This is the syntax I used to detect a boolean value:

    REPLACE(REPLACE(field, 1, “Yes”), 0, “No”)

    Hope this helps!

  22. […] As part of my take over the world campaign I’ve run into the need to automatically do a global Search and replace within a Mysql Database covering all tables.  For this you need a little php magic and luckily enough I found just that magic right here. […]

  23. Guest
    Apr 25, 2011

    I used the script with GUI – many thanks for your part in this. Pure genius to a person like me, who has only a centimeter worth of knowledge (kilometers wide though), but I can still drown in code more than a line deep.

  24. Sacameunojo
    May 05, 2011


    You are my hero!

  25. Ligi
    Jun 01, 2011

    great, tnx a million! my client changed all their flower names in a CMS. I was hoping not having to do it manually….

  26. Ligi
    Jun 01, 2011

    Just used it on the live site, one thing to keep in mind : if the replace involves image names in the database, then you will have to adjust the filenames of the images on the server….

  27. Guest
    Sep 10, 2011

    Thank you! You saved me load of time.

  28. PerfectAnswer
    Oct 14, 2011

    Thank you for sharing this great script!
    Runs like a charme, very well inline documentation, good looking output.
    Very Happy with your script.
    Regards from Hamburg/Germany

  29. Urselinho
    Nov 07, 2011

    Thanks alot…will save me a lot of time!!!!

  30. alberto
    Jan 20, 2012

    Great script! thanks a lot.
    I have just one suggestion.I used this script to do a search and replace in my magento db, and some column names have reserved words, so I needed backticks  in order to make it work.Just changed 
     $sql[$handle]['sql'] = 'UPDATE '.$table.' SET '.$field.' = REPLACE('.$field.',''.$search.'',''.$replace.'')';
         $sql[$handle]['sql'] = 'SELECT * FROM '.$table.' WHERE '.$field.' REGEXP(''.$search.'')';
    on lines 88 and 90

  31. […] için Mark Jackson tarafından yazılmış php betiğini […]

  32. Neil
    May 25, 2012

    Hi MJdigital,

    Just wanted say a huge THANK-YOU for this script. It’s a lifesaver!


  33. Themajstor
    May 30, 2012

    Thx, man. U save me tons of time.

  34. […] Di internet saya pernah menemukan sebuah tutorial beserta script PHP yang memungkinkan hal ini dapat dilakukan, berikut script php yang digunakan […]

  35. Mohit Arora
    Jul 18, 2012

    Nice work dude.

  36. Prashant
    Sep 25, 2012

    Thanks a ton worked like a charm !!!

  37. Anand
    Mar 14, 2013

    Thanks for the good script.

  38. y0d4
    Apr 04, 2013


    great script man! 🙂
    but i have little problem, when i fill up all details and click “go” i non-stop getting this info:

    Cannot Query DB: No database selected

    any suggestion?

    thank you.

  39. Ben
    Apr 28, 2015

    Man!!!!! You are the best! Thank you so much