Virginia Web Designers: Joomla, Drupal, Open-Source, CSS, and PHP Northern Virginia / Maryland / Washington DC web designers

Search and replace in your MySQL database

Imagine this scenario:

You have an existing company web site with well over 100 pages, and it uses a MySQL database. For argument’s sake, let’s say it runs Joomla. Now suppose you have a product and have recently re-branded it, and now it has a new name. It appears in 300 places in various pages. How can you quickly replace all those instances?

Using this query in phpMyAdmin;

UPDATE jos_content SET introtext = REPLACE(introtext,’Old Product Name’,'New Product Name’);

UPDATE jos_content SET introtext = REPLACE(introtext,’Old Product Name’,'New Product Name’);

UPDATE jos_modules SET introtext = REPLACE(introtext,’Old Product Name’,'New Product Name’) WHERE module=’mod_custom’;

That should take care of 99% of those instances.

Of course, BACK UP before running this query! You can do that in phpMyAdmin by clicking the Export tab at the top (when you are viewing your database). I always select the option for Complete Inserts and Extended Inserts, and Save as File. You may also need to select the tables you want to export. If it is a very large database, it may make sense to choose “gzipped” (for gzip compression).  It offers much better compression than zip.

Many web hosting control panels offer phpMyAdmin. In cPanel, there should be a section called Databases, with phpMyAdmin listed as one of the icons. Click that to manage your existing databases, and then select the one you need to edit – on the left. To run the query, click the SQL tab at the top and paste the query, and click Go (on the bottom right).

Using queries such as the one above can potentially save many hours of work, if done right and done carefully.

There is a commercial Joomla component (here) that does the same thing as the above query, except I’m not sure if it also processes module text. However, performing database queries such as this one really do require backing up the database beforehand, and although there are Joomla add-ons that allow for easy database backup within the administrator interface, I think for redundancy and simply being safe rather than sorry, you also need to back up via phpMyAdmin, and if possible also grab a backup from cPanel (choose the “Home Directory” backup). So then you may as well run the query yourself in phpMyAdmin. If you’re uncomfortable doing that, try running the query on a copy of the site first, perhaps using XAMPP on your personal computer.