How to ‘Find and Replace’ in MySQL with phpMyAdmin

Posted on: June 3rd, 2015 by BuildMyWeb No Comments

The Problem:

You have a large number of image files stored on your hosting account and want to move them to a new subdirectory. However, you have a database that stores the absolute path to each of these images files and do not want to go through the tedious task of manually checking and editing hundreds or thousands of table rows.

The Solution:

You can Find-and-Replace strings of text in your mysql database tables just like you would in a MS Word document. Log into phpMyAdmin and select your database.

The Procedure

The first thing I would do is make a backup of your database. Or alternatively, backup the Table within the database you are going to be working on. Keep that safe to restore your table/database should something go horribly wrong. If you don’t know how to do this, you really shouldnt be working in your database at all. Contact a professional to help you if you hold any value on your database because it could all blow up rather easily. And without proper backups, there is no restoring your data. Databases do not have a Recycle Bin like your Windows OS. Once you make a change, it is permanent.

phpmyadmin

Now that you have your parachute tucked away nice and safe, you will want to navigate to the table in question and click on the SQL tab toward the top of the screen to run a SQL query. In the snippet below, you will find an example. Of course, replace the italicized text with your actual values.


UPDATE table_name SET field_name = REPLACE(field_name, 'original_text', 'new_text');

This will search out and find every instance of original_text and replace it with new_text in the field with the name field_name in the table named table_name. In our hypothetical problem for this article, let’s suppose we are editing a WordPress database. We need to replace an old URL with a new one so we might run a query something like this:


UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://mydomain.com/wp/uploads/15/03', 'http://mydomain.com/blog/uploads/15/03' WHERE post_status = 'publish');

Your final step is to click the GO button at the bottom right of your SQL query frame. If you followed my instructions, all should go well. Good luck!

Tags: ,

Leave a Reply