Brady J. Frey

Search bradyjfrey.com
Search my site

How to find and replace in MySQL PHPmyAdmin

Full Article:

Just because someone asked, here’s a quick way to find and replace content in MySQL using PHPmyAdmin. This technique works the same command line, and it’s possible it works similar in applications such as Navicat, but I have little experience in GUI tools. To manage:

  1. Login to PHPmyAdmin
  2. Select the database you want in the left frame, if you have multiple
  3. Choose the ‘SQL’ tab in the right frame
  4. Add the following code and update
UPDATE `table` SET tablefield = replace(tablefield,"your mom","my mom");

Replace table with the name of your table, the tablefield with the title of the tablefield you want to search, the first value (your mom) will be replaced by the second (my mom). In my case, I zapped gremlins for emdash in a standard wordpress 2.5 upgrade:

UPDATE `wp_posts` SET post_content = replace(post_content,"รข","—");
Similar Posts

Published

9 Reader Comments::
  1. 1 Updating To Wordpress 2.5: Stop Character Replacements « Douglas Hahn says:

    [...] solution was recommended by a friend , but I don’t like using PHPmyAdmin if I don’t have [...]

  2. 2 Douglas says:

    Just an update, I found the easy solution after you emailed me:

    Delete these two lines from your wp-config file:

    define(‘DB_CHARSET’, ‘utf8′);
    define(‘DB_COLLATE’, ”);

    … and that’s it! Took less than a minute, and Sink’s looking gremlin-free again.

    Thanks for helping here, Brady!

  3. 3 James T says:

    Hi, Thanks for the sql tip it saved me a lot of time changing a users name in the wp_comments table under comment_author
    Affected rows: 156 (Query took 0.0751 sec) :-)

  4. 4 Dave says:

    I have been trying to figure this out for a while as I am new to having to work on MYSQL. Your description was the easiest after looking at several sites. Thanks a bunch. Remember that some newbies need step by step directions.

  5. 5 Brady J. Frey says:

    Glad it could help you guys out! I should update this in the near future with some grammar fixes, a bit dated now:)

  6. 6 Shea says:

    Hey there!

    Thanks much for this! I’ve just got one question: Is there a way to search all fields in a table for a find and replace? Or even better, all tables in a database?

    Thanks!

  7. 7 Darren Anthony says:

    Thank you very much! This has really saved my day.

    I have been wanting to move my images folder to a different location on my server, but I would’ve needed to update every post manually.

    Your post has just made my job a lot easier. It only took a few seconds to update my database.

    Thanks a million :o )

  8. 8 Brady J. Frey says:

    You’re very welcome Darren, glad it could be of help!

  9. 9 MySQL Find & Replace | Diary Of An ADI says:

    [...] started to edit them out, then realised there must be a better way. Thanks to Brad J Frey’s blog, I fixed it in about 15 [...]

Your thoughts:
Tell us what you think: