PhpMyAdmin Search and Replace Text in Your Database

PhpMyAdmin Search and Replace

I’ve just tried to do a search and replace using phpMyAdmin and guess what! There is no search and replace in phpMyAdmin! Considering almost every text editor, word processor and database application I use (other than phpMyAdmin) has this function this seems a rather obvious anomaly. Anyone know why the phpMyAdmin search and replace function is missing?

In my case, I needed the KingSolutions WordPress site to run on my local computer with the ‘http://kingsolutions.org.uk/‘ text replaced with ‘http://localhost/’ or more simply ‘/’ so I could test a particular plugin without the possibility of any references to the live site.

Anyway, here’s the solution to the problem of the missing phpMyAdmin search and replace function that can save you hours of work.

So, if you’ve messed up characters in your database, want to strip out some text in a certain table’s field or need to replace text then do the following.

• In phpMyAdmin, select the database you want to modify.
• Select the [Search] tab and check the location of the text you want to replace (if you don’t know already). Don’t forget to select either specific or All tables.
• Once you have the tablename and fieldname info, select the [SQL] tab and use the following SQL statement.

 

UPDATE tablename SET fieldname = REPLACE(fieldname, 'findstring', 'replacestring');

 

All you have to do now is hit the “GO” button and phpMyAdmin will take care of the rest.

• Note: Don’t forget to change the tablename, fieldname, findstring and replacestring to your values :).

You can also add a WHERE clause onto this query if needed as well.

 

UPDATE tablename SET fieldname = REPLACE(fieldname, 'findstring', 'replacestring') WHERE 'something' REGEXP 'equalsthis';

 

Here’s an explanation of the code for new WordPress and MySQL users:

• tablename: This is the name of the table you’re going to update.
• fieldname: (twice) Where to search and make the update.
• findstring: The specific text you are looking to replace.
• replacestring: The replacement text.

Example

In this example I’m going to replace all instances of the word “colour”, the UK spelling, with “color”, the US spelling, in the body content of all posts and pages.

• This means the tablename will be wp_posts and the fieldname will be post_content.

My SQL statement would look like this:

 

UPDATE wp_posts SET post_content = REPLACE(post_content, 'colour', 'color');

 

A Few Things to Keep in Mind:

• Make a backup of your database first. There is no “undo”.
• Make sure your statement is accurate, otherwise, you’ll be doing a find and replace for the mistake you just made.
• Tread carefully. The phpMyAdmin interface, presumes you know what you are doing.
• Changes are immediate. Your changes should be immediately visible unless you’re using a caching system.

Finally, if you have any thoughts or suggestions relating to phpMyAdmin Search and Replace, don’t forget to let me have them in the comment box below. Thanks for dropping by.