brock

Search and Replace a Custom Field in WordPress using PHPMyAdmin

18 Mar 2009

If you've ever used Microsoft Excel, you probably have an idea of just how easy changing hundreds of cells can be. Gone are the days of manually typing line by line any corrections that you need to make. The same is true when you are working with a database like we are in WordPress.

This example performs a search and replace of all custom fields that meet a specified criteria. In this case, I'm looking for all staff member pages that have a custom field for a department phone number that was incorrect. We want to replace that with a new number. Using PHPMyAdmin and running a SQL query, I can select all of them to see the results before I do anything to my databse:

SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE 'DeptPhone' AND `meta_value` LIKE '212-555-1212'

It is worth mentioning, that you can also use wildcards if you want to include more results. The '%' is your wildcard:

SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE '%eptPhone' AND `meta_value` LIKE '212-555-%'

This yields the same results. It is also worth pointing out that the quotation mark around our text values and the tick mark around our table column names are different.

So, to perform the changes, we need to use the UPDATE command instead of select, and actually replace text. Here goes:

UPDATE `wp_postmeta` SET `meta_value` = replace(meta_value, '212-555-1212', '212-444-1212') WHERE `meta_key` LIKE 'DeptPhone'