Find, Search, Replace, and Delete in the WordPress Database

The following was originally published on WordCast and authored by Lorelle VanFossen. It is reprinted here as a reference guide.

1. You’ve moved your WordPress installation from one server to another.
2. You’ve changed domain names.
3. You’ve moved images around on your server and now they don’t load.
4. You’ve changed your WordPress installation and now images show blank boxes on your posts.
5. You’ve changed your email address.
6. An author has changed their name or URL.
7. You’ve been asked to remove all of someone’s blog comments.
8. You’ve turned things off during development and it’s time to turn them back on.
9. You’ve gotten smart and changed your WordPress URL and installation from example.com/wordpress/ to example.com.
10. You’ve realized that you’ve been misspelling “separate as “seperate” and you now need to fix all the missed spellings.
11. You’ve realized that WP as an acronym isn’t as SEO friendly as you thought and decide to change them all to WordPress.

These are just a few of the reasons why you need to know how to do a search and replace in the WordPress MySQL database. Some people choose to export their entire WordPress site’s data and edit it in a text editor, but for those with hundreds or thousands of posts, it’s easier to do it in the MySQL database.

Playing with your WordPress MySQL database is not for the weak of heart and faint of code. There is much to fear. The worse case scenario is that you blow up your site. The best scenario is that you fix some problems that have been plaguing you. I’ve some warnings below, but trust that even someone with little coding experience can do this, if you are very, very careful and take precautions.

The following search and replace and delete WordPress-related queries for MySQL are included:

• The Process of Searching and Replacing in the MySQL Database
• Search and Replace Blog Content
• When You’re Not Sure of the Unique Value to Search and Replace
• How to Search and Replace Domain Names
• How to Search and Replace Image Location Issues
• How to Search and Replace Ad or Affiliate Links
• How to Search and Replace Nofollows
• How to Search and Replace Administrator Username, Passwords, and More
• Changing a Post Author’s Name or Information
• Cleaning Up Your Spellings, Cusses and Ps
• How to Search and Replace Within the WordPress Custom Fields
• How to Change WordPress Table Prefixes
• How to Search and Remove Blog Comments
• Overloaded with Comment Spam?
• How to Enable Functionality in WordPress from Within the Database
• Create a Mailing List of Commenters
• Cleaning Out Unused Tables from WordPress Plugins and Add-ons
• More Resources and Tips for Search and Replace in the WordPress Database

Some Things to Consider Before Touching the WordPress Database

This guide will tell you how to search and replace in the WordPress database manually, however it comes with some caveats. Please read through these carefully.

• If you only have a few simple changes to make, do them manually. Using this technique isn’t worth the risk and effort.
• You’ve done extensive and thorough (and triple checked) backups of your database and content (export).
• Use this if the changes you are making are unique terms and phrases. Don’t just change wordpress to /wordpress thinking all “wordpress” words in your URLs will change. You could find all your tutorials about WordPress saying something like “when you install /wordpress on your site” instead of “when you install WordPress on your site” or links to the WordPress official site in your posts would become http://www.//wordpress.org/. Search and replace ONLY unique content to avoid blowing up everything.
• You test frequently between search and replaces to ensure your site is still functioning and no errors are reported.
• For multiple search and replace efforts, after testing, backup a new version just in case the next change you make breaks things. You only have to restore one or two steps back.
• You sincerely trust your ability to use code on your database.
• Do this only if you are willing to live with the consequences and wasted time and effort if you mess things up.

There are a variety of tools you can use before you should dig into your WordPress database. The Find Replace, Search Regex, and Search and Replace WordPress Plugins may be considered as an alternative to manually executing a search and replace in the WordPress database. The MySQL Search & Replace Tool for WordPress by SewMyHeadOn is another non-WordPress Plugin option. These are WordPress-based tools, so they restrict search and replace access to specific WordPress tables.

NOTE: These examples in this article feature a variety of table prefixes, such as wp_ (default), mywp_, and mywp_18_, are representative of a secure WordPress or multiple site network installation. Please check thoroughly for your table prefix and use that in the code.

The Process of Searching and Replacing in the MySQL Database

The process to search and replace in the MySQL database is as follows, with specific examples below.

1. BACKUP YOUR DATABASE.
2. Go to phpMyAdmin, cPanel, or other method to open your WordPress database and find the tables for your specific WordPress installation.
   • NOTE: Individual WordPress blog tables typically have a table prefix of wp_posts. Check yours to see if it uses a unique table prefix such as mywp_posts. If you are using the multisite version of WordPress (Blog Network or WordPressMU), the tables are designated by Blog ID number such as wp_18 or mywp_18_posts. Check thoroughly to ensure you are making changes to the correct tables and site.
3. Use the following search and replace command structure:

   UPDATE tablename SET `fieldname`
   = REPLACE (`fieldname`,
   'Item to replace here',
   'Replacement text here');

   • NOTE: Around the table and/or fieldname are backticks created by pressing the key to left of number 1 on your keyboard. The apostrophe single quote marks must be around the text to replace and replacement text. These are required in recent versions of MySQL.
4. Check that everything is correct – there is no going back or undoing.
5. Click GO to begin the search and replace.
6. The results will inform you of how many records were changed and permit you to view the changed records.

The search and replace features in MySQL are limited, making the process of doing global or wildcard changes frustrating. Things you cannot do with a MySQL search and replace include:

• Cross table search and replaces.
• Search and replace field names (values yes, names no).
• Search and replace using wildcards (but you can use the LIKE argument for some searches).
• Search for values in one row and use them in another.

Here are examples of what you can do within the WordPress MySQL database.

Search and Replace Blog Content

To search and replace within the post content area of WordPress, use the above guides and the following code, checking that the table and field names match exactly to the blog you wish to change.

UPDATE wp_posts SET `post_content`
= REPLACE (`post_content`,
'Dancing Quietly in the Night',
'Walking Loudly in the Day');

To change a URL within the post content area of WordPress, such as a site you commonly link to changing domain names or you change your own, use the following. URLs such as http://example.com/2010/05/my-post-title will only change the domain name to http://myexample.com/2010/05/my-post-title.

UPDATE wp_posts SET `post_content`
= REPLACE (`post_content`,
'http://example.com/',
'http://myexample.com/');

When you redesign your site, you often want to change something in the styles of the post content. For instance, many add the external style to outgoing links within their site to make these appear different from intrasite links, warning readers that this link will take them off their site so they may want to open the link in a new tab or not.

The change to a link would be:

<a href="http://example.com/" title="Link offsite">link text</a>
to
<a class="external" href="http://example.com/" title="Link offsite">link text</a>

The problem with such a search and replace is the lack of a unique search term. If you just search and replace every instance of <a href=" with <a class="external" href=", every link on your site would be changed. So a little game needs to be carefully played to make this work.

1. Identify intrasite links with a CSS class for internal. You can use any word you wish, such as home, homesite, sitelinks, or something more SEO friendly like MySiteName. Do a search and replace for all links on your site that includes your site’s domain name URL.

   UPDATE wp_posts SET `post_content`
   = REPLACE (`post_content`,
   '<a href="http://example.com/',
   '<a class="internal" href="http://myexample.com/');

   Only links with URLs linking to your site would be changed.

2. Do another search and replace on all the other links to add the external CSS class.

   UPDATE wp_posts SET `post_content`
   = REPLACE (`post_content`,
   '<a href="http://',
   '<a class="external" href="http://');

By making the internal links unique and different from outgoing links, an environment is created for a quick search and replace.

When You’re Not Sure of the Unique Value to Search and Replace

When working with a phrase or multiple words, it’s often hard to know if you set them in the sequence of word1 word2 word3 or word2 word1 word3. If you are having trouble seeing the redundant unique values, run a search to look for “like” terms, then make your search and replace decisions accordingly.

For instance, if you want to change two CSS styles but you aren’t sure if you used them as class="style1 style2" or class="style2 style1, you can run a LIKE search to filter out the possibilities and create your search and replace accordingly.

SELECT * FROM mywp_posts WHERE (
post_content LIKE '% style1"%'
OR post_content LIKE '% style2%'
OR post_content LIKE '%style1 %'
OR post_content LIKE '%style2 %'
);

This would give you a search result that you could study to see which sequence you used, or not, and create a search and replace accordingly so that the style orders would all be the same, such as:

UPDATE wp_posts SET `post_content`
= REPLACE (`post_content`,
'class="style2 style1"',
'class="style1 style2"');

If you then wanted to add a third style or take one away, you would have a consistent set across all of the database. Let’s add a style in this example.

UPDATE wp_posts SET `post_content`
= REPLACE (`post_content`,
'class="style1 style2"',
'class="style1 style2 style3"');

Search and Replace Domain Names

If you change your site’s domain name, it helps to change it everywhere. MySQL doesn’t permit cross table or field search and replace actions, so you have to do this one at a time. You have to change the domain name in the post content, comments, excerpts, user URLs, emails, and other post meta data tables. Here is a good list of the queries, initiated individually.

UPDATE wp_posts SET `post_content`
= REPLACE (`post_content`,
 'www.example.com', 'www.newexample.com');

UPDATE wp_posts SET `post_excerpt`
= REPLACE (`post_excerpt`,
 'www.example.com', 'www.newexample.com');

UPDATE wp_users SET `user_url`
= REPLACE (`user_url`,
 'www.example.com', 'www.newexample.com');

UPDATE wp_users SET `user_email`
= REPLACE (`user_email`,
 '@example.com', '@newexample.com');

UPDATE wp_options SET `option_value`
= REPLACE (`option_value`,
 'www.example.com', 'www.newexample.com');

UPDATE wp_postmeta SET `meta_value`
= REPLACE (`meta_value`,
 'www.example.com', 'www.newexample.com');

UPDATE wp_comments SET `comment_author_url`
= REPLACE (`comment_author_url`,
 'www.example.com', 'www.newexample.com');

UPDATE wp_posts SET `guid`
= REPLACE (`guid`,
 'www.example.com', 'www.newexample.com');

UPDATE wp_usermeta SET `meta_value`
= REPLACE (`meta_value`,
 'www.example.com', 'www.newexample.com');

Remember that you will need to also change your image URL paths to reflect the change in the domain name.

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
 'src="http://www.example.com', 'src="http://www.newexample.com');

The guid table also stores the new Post Type functionality and will need to be changed to permit images to appear in “attachment” posts:

UPDATE wp_posts SET `guid`
 = REPLACE (`guid`,
 'http://www.example.com', 'http://www.newexample.com')
 WHERE post_type = 'attachment';

Check with the current versions of WordPress to ensure you’ve covered all the possible fields.

Search and Replace Image Location Issues

Many WordPress migration and import situations make a mess of image links. Ensure the images are in the server directories in as close as the original location as possible, then run a search and replace on the link URLs to adjust them to the right settings.

For images you’ve uploaded using older versions of WordPress or through FTP programs, a simple search and replace for the image location might be:

UPDATE mywp_posts SET `post_content`
= REPLACE (`post_content`,
'http://example.com/images/',
'http://example.com/photos/');

Recent versions of WordPress featuring the Media Library permit uploading of images through the WordPress Administration Panels and set the images in folders by year then month. Move the images within these subdirectories as intact as possible, but let’s say that you move the images from month 08 (August) to 12 (December) and just want to leave them there.

UPDATE mywp_posts SET `post_content`
= REPLACE (`post_content`,
'http://wordcastnet.com/wp-content/uploads/2010/08/',
'http://wordcastnet.com/wp-content/uploads/2010/12/');

Take care to be as specific as possible when making such directory search and replace changes. Simply changing /2010/08 to /2010/12 could also change all permalinks to posts with that similar URL structure, not just the image locations.

Images not in the Media Library but placed in the right directory on the server will appear in old posts. If you wish to use them again, you can copy the location from the old post, or upload the image again.

After a move or change in the directory structure, if the WordPress Media Library lists the images but doesn’t show thumbnails, it means the images aren’t in their proper directory, or the links to the previously uploaded images are incorrect in the database. As mentioned, the guid table stores the location of previously uploaded images and will need to be updated with the new location. Once these are changed the images will appear in the Media Library:

UPDATE wp_posts SET `guid`
 = REPLACE (`guid`,
 'http://www.example.com/', 'http://www.newexample.com');

Search and Replace Ad or Affiliate Links

There will be times when ads placed within post content will need to change due to a change in your ad program, ad program ID, or a replacement of the whole ad content and link. This only impacts ads or affiliate links within the post content area, not those within the WordPress Theme or installed through the use of a WordPress Plugin.

UPDATE mywp_posts SET `post_content`
= REPLACE (`post_content`,
'<a href="http://exampleads.com/adnetwork/123456ABC" title="Link to My Ad">
<img src="http://exampleads.com/image.png" alt="Ad Image" /></a>',
'<a href="http://exampleads.com/adnetwork/ABC987654" title="Link to My Ad New">
<img src="http://exampleads.com/image2.png" alt="Ad Image Two" /></a>');

To replace the entire ad and image link with nothing – a blank space – use the following example.

UPDATE mywp_posts SET `post_content`
= REPLACE (`post_content`,
'<a href="http://exampleads.com/adnetwork/123456ABC" title="Link to My Ad">
<img src="http://exampleads.com/image.png" alt="Ad Image" /></a>',
'');

The '' as the replacement text replaces the search terms with nothing.

Some bloggers and WordPress Plugins use WordPress custom fields for their ads. To change a variable in a custom field through a search and replace in the MySQL database, use the following example which looks for the meta_key “AdId” and replaces the meta_value with the new Ad ID.

UPDATE mywp_postmeta SET `meta_value`
 = Replace (`meta_value`,
'123456', '987654') WHERE `meta_key` LIKE 'AdId';

To Search and Replace Nofollows

While follow, dofollow, and nofollow – attempts to take away SEO “credit” from links within blog content and comments – don’t work, some still are playing the game and turning their sites into “Dofollow” fan clubs, encouraging readers that links in their comments will give them the link juice love they deserve. Some are using WordPress Plugins, which may not remove all of the nofollow attributes from content and comments published in the past, so you may wish to permanently remove them as well with a search and replace for follow attributes.

This MySQL script will search and replace for nofollow attributes within all post content, though a Plugin must be used to remove the nofollow from comments such as WP-Dofollow WordPress Plugin and/or SMu Manual DoFollow WordPress Plugin.

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
'nofollow ', '' );

Search and Replace Administrator Username, Passwords, and More

Occasionally it can be difficult to make changes to the administrator records of WordPress, requiring a visit to the MySQL database.

To change the default “Admin” username, highly recommended for security protection:

UPDATE wp_users SET `user_login` = 'MasterAdminoftheWorld'
WHERE user_login = 'Admin';

For the rare case when you can’t get your password reset, setting the password without spaces:

UPDATE wp_users SET `user_pass` = MD5( 'new-password' )
WHERE user_login = 'theusernameofperson';

Changing a Post Author’s Name or Information

People change their names occasionally through their lives, but with bloggers, some name changes are to protect or reveal the author. Many bloggers use pseudonyms, creating an anonymous identity with a funky name like a bad CB handle – musiclover, prettychiq69 – and sometimes they come out from behind the curtain, or decide to hide behind it, thus a change to the post author’s name on your site is needed.

To replace the post author name in WordPress, go to the Administration Panels and change the name in the author’s profile, also changing the display name. This will update all the bylines automatically throughout the site, including on author pages and author lists. This will not change the name references in links and text throughout the blog’s content.

To change the name of the author throughout the post content:

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
'Sally Smith',
'Fred Smith');

To change the author URL links within the content that points to posts archives by that author:

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
'http://example.com/author/sally-smith/',
'http://example.com/author/fred-smith/');

Here are more examples to quickly go through your entire site and change every name reference to the right one, just in case the post content areas aren’t enough:

UPDATE wp_comments SET `comment_author_name`
 = REPLACE (`comment_author`,
'Sally Smith', 'Fred Smith');

UPDATE wp_posts SET `post_excerpt`
= REPLACE (`post_excerpt`,
'Sally Smith', 'Fred Smith');

UPDATE wp_users SET `user_email`
 = REPLACE (`user_email`,
'sallysmith@sally.com', 'fredsmith@fred.com'');

UPDATE wp_comments SET `comment_content` =
REPLACE (`comment_content`,
'Sally Smith', 'Fred Smith');

Once in a rare while, an author will leave and the leaving will require all articles written by them to be transferred to another author, possibly a site “team” of authors rather than an individual.

To transfer all articles from one author to another, you must have the author ID for both authors.

UPDATE wp_posts SET `post_author` = 'new-author-id'
 WHERE post_author = 'old-author-id';

Cleaning Up Your Spellings, Cusses and Ps

While the latest versions of WordPress force the word WordPress or wordpress to the proper trademarked spelling of WordPress, many want to go back and fix the form permanently throughout their blog posts and post titles. This is a much more complicated search and replace as MySQL search and replace functions do not recognize capital letters, nor recognize when the term is in a link or a sentence.

A simple search and replace of WordPress for WordPress would convert simple links like http://example.com/wordpress/2008/08/my-wordpress-post-title/ into http://example.com/WordPress/2008/08/my-WordPress-post-title/ which could result in some very odd URLs and possible 404 errors, though WordPress’s canonical links should handle them.

The key is to make the search term unique, so you look for a space before then a space after the word to replace the term.

To search and replace the term with the space before the word:

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
' wordpress',
' WordPress' );

To search and replace the term with the space after the search term:

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
'wordpress ',
'WordPress ' );

You can do the same thing in your post titles and excerpts using the following example with the space after the search term:

UPDATE wp_posts SET `post_excerpt`
 = REPLACE (`post_excerpt`,
'wordpress ',
'WordPress ' );

UPDATE wp_posts SET `post_title`
 = REPLACE (`post_title`,
'wordpress ',
'WordPress ' );

You can use this technique with other similar situations when you need to change the word while protecting your URLs.

On the rare occasion you decide to find religion or clean up your cussing and swearing with a change-of-life decision, you can search for swear words and replace them permanently with asterisked spellings.

Again, this is a dangerous search and replace method, so backup thoroughly and do this very carefully. For example, if you are cleaning up the word “shit” you might be a crafter or singer and used the word “shittle,” “shittlecock,” or another word that has “shit” in the middle of it that isn’t a “bad” word. For example, the word “cuss” is found in discuss, discussion, and percussion, words you wouldn’t want to see become disc*ss. It’s much easier to use the WP Content Filter WordPress Plugin to filter out the “bad words.” Still, this is a good example of how to find a unique phrase for search and replace.

To avoid this, use the space before and after the word as shown above. This examples looks for the space before the word and a space after and changes it from the bad to the safe version while keeping the intent.:

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
' shit ',
' sh*t ' );

This example looks for the word with a space before it and a period after it. You can do this with a variety of punctuation such as question marks and exclamation marks.

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
' shit.',
' sh*t.' );

For examples such as “discuss” caught in a “cuss” search and replace, you have a greater problem replacing the cuss as it often comes with a space after it. Unlike a word processing program that shows you each found search and asks you to replace it, MySQL doesn’t. This is when you need to use the SEARCH filter and manually change all the instances.

Search and Replace Within the WordPress Custom Fields

Occasionally, you will change WordPress Plugins or your WordPress Theme and you need to make a change to your WordPress Custom Fields. For example, you decide to switch your WordPress Mood Plugin which uses “attitude” instead of “mood” to represent your mood at the moment, so you need to replace the meta_key but not the meta_value which might remain the same for happy, sad, miserable, greedy, whatever.

UPDATE mywp_postmeta SET `meta_key`
= REPLACE (`meta_key`,
 'mood', 'attitude');

To change a value within the WordPress Custom Fields, such as deciding you never had a greedy day in your life so you want to change it to “successful,” you would run this search and replace:

UPDATE mywp_postmeta SET `meta_value`
 = Replace (`meta_value`,
'greedy', 'successful')
 WHERE `meta_key` LIKE 'attitude';

Changing WordPress Table Prefixes

The table prefix is set in the wp-config.php file in the root of your WordPress installation. By default, it is set as wp_. To change it to something else, edit the wp-config.php file in a text editor accordingly:

$table_prefix='wp_';

to

$table_prefix='mywp_';
;

This only changes it in the configuration file. You must also change it throughout your entire database so all the tables change from wp_ to mywp_ using the RENAME TABLE Syntax. Each line must be added to the SQL window individually and initiated.

Rename table wp_commentmeta TO mywp_commentmeta;

Rename table wp_comments TO mywp_comments;

Rename table wp_links TO mywp_links;

Rename table wp_options TO mywp_options;

Rename table wp_postmeta TO mywp_postmeta;

Rename table wp_posts TO mywp_posts;

Rename table wp_term_relationships TO mywp_term_relationships;

Rename table wp_term_taxonomy TO mywp_term_taxonomy;

Rename table wp_terms TO mywp_terms;

Rename table wp_usermeta TO mywp_usermeta;

Rename table wp_users TO mywp_users;

A key issue with changing the rest of the table prefixes is that wp_ isn’t unique, so some manual search and replace queries are needed to update the rest. The first search and replace is in the Options table. Note it has the new prefix.

UPDATE mywp_options SET `option_name`
 = Replace (`option_name`,
'wp_user_roles', 'mywp_user_roles');

The next batch, initiated one at a time, these “global” search and replace instructions should change record names and table prefix references for roles and permissions, user meta, user levels, user settings, and autosave settings.

UPDATE mywp_usermeta SET `meta_key`
 = Replace (`meta_key`,
'wp_', 'mywp_');

UPDATE mywp_usermeta SET `meta_value`
 = Replace (`meta_value`,
'wp_', 'mywp_');

To double check to see if you covered all of the table prefix changes, run a search for all instances of wp_ in the site. Use the SEARCH tab to initiate this search and add this in the search conditions for the following tables:

• mywp_usermeta
• mywp_options

meta_key like 'wp_%'

To really triple check your site for all references to the old table prefix, you can run a global search.

• Click the database name at the top of the left column in phpMyAdmin to select the entire table.
• Click the Search tab on the right top.
• In “Word(s) or value(s) to search for” type in wp_.
• Choose “Find the exact phrase.”
• Choose Inside Tables: Select All.
• Click GO.

The results will show you which tables and fields still have the wp_ prefix. Edit these carefully through the Browse feature if necessary as they may refer to WordPress Plugins and non-table references. If your site breaks, restore your database and try it again.

Search and Remove Blog Comments

Rarely, a blogger may be asked to remove all comments by a specific commenter. This can happen if someone requests it due to privacy reasons, legal action, or if someone is banned from your community. To delete all comments within a WordPress blog by a specific person, you can do so if you have one of the following:

• Author’s exact name
• Author email
• Author URL
• Author IP

You can do a search with MySQL to track down any of the specific data. Remember to be VERY specific as this cannot be undone. Do not use this lightly as the risk to your site’s database is too great.

To delete the comments by the commenter’s exact name used within the blog comments form:

DELETE FROM `mywp_comments`
WHERE `comment_author`
= 'Fred Smith';

To delete the comments by the commenter’s email address:

DELETE FROM `mywp_comments`
WHERE `comment_author_email`
= 'Fred Smith';

To remove the comments by the commenter’s URL:

DELETE FROM `mywp_comments`
WHERE `comment_author_url`
= 'http://example.com/';

To remove the comments based upon the IP address of the commenter:

DELETE FROM `mywp_comments`
WHERE `comment_author_IP`
= '12.345.678.90';

Overloaded with Comment Spam?

Before moving your blog from one host to another, or around on your server, or maybe you are just ready to do a major clean out of comment spam from your blog instead of waiting for Akismet to handle it automatically, you can manually tell the MySQL database to remove all the spam comments from your site.

To remove all the spam comments, designed as spam:

DELETE FROM `mywp_18_comments`
WHERE `comment_approved`='spam';

To remove all comments not labeled as spam but set to 0, which means that they are most likely spam but an older bug in Akismet didn’t set them right. Use the latest version of Akismet to fix this issue, but if you find spam comments marked as zero, use this:

DELETE FROM `mywp_18_comments`
WHERE `comment_approved`='0';

To clean out all the comments marked as trash:

DELETE FROM `mywp_18_comments`
WHERE `comment_approved`='trash';

If you feel brave, and have a solid backup (or three), you can also clear out all the posts set as trash within the WordPress Database, though this is not recommended.

DELETE FROM `mywp_18_posts`
WHERE `post_status`='trash';

To Enable Functionality in WordPress

During development of a WordPress site, we often turn off comments, pings, and trackbacks. Since there isn’t always a clean way to turn these back on on all published posts, a search and replace of the off status must be run on the WordPress database tables.

To turn the ping status back on, use this search and replace shortcut:

UPDATE wp_posts
SET ping_status ='open';

To enable comments on all blog posts with comments closed, use:

UPDATE mywp_18_posts
SET `comment_status` = 'open', ping_status = 'open'
WHERE comment_status = 'closed';

To close comments on all blog posts, use:

UPDATE mywp_18_posts
SET `comment_status` = 'closed', ping_status = 'closed'
WHERE comment_status = 'open';

To set posts to draft instead of published:

UPDATE mywp_18_posts
SET `post_status` = 'draft'
WHERE (post_status = 'publish' AND post_type = 'post');

To set posts set as drafts to publish:

UPDATE mywp_posts
SET `post_status` = 'publish'
WHERE (post_status = 'draft' AND post_type = 'post');

Create a Mailing List of Commenters

There are WordPress Plugins to create newsletters and emails to those who comment on your blog, but you can also create an exported list of all the commenters emails:

SELECT DISTINCT comment_author_email FROM wp_comments;

Overweight Database: Cleaning Out WordPress Plugin Tables and More

Only recently are a few WordPress Plugins offer an “uninstall” feature which removes all the left over and unwanted data when you are done using a WordPress Plugin. If you’ve been using WordPress and many WordPress Plugins over the years, you probably have a bit of an overweight database that could do with a little weight loss program as well as spring cleaning.

The Clean Options WordPress Plugin cleans up your wp_options table to remove orphaned options from past Plugins, making the job nice and easy. WP-DBManager WordPress Plugin also helps you to optimize and repair your tables, as well as backup, drop/empty tables, search and replace, and run other selected queries.

If you know what you are doing, you can use the MySQL query for DROP TABLE, but this will only remove the table and not references within other tables and fields, possibly making a bigger mess, so do this with care as there is no way to do a wildcard search and replace in MySQL.

More Resources and Tips for Search and Replace in the WordPress Database

Not everything can be searched and replaced easily within a WordPress database. Categories and Tags are an example. The new taxonomy and structure of categories and tags makes a simple search and replace a complex nightmare, crossing multiple tables and fields. To restructure your categories, there are few options available, but try Bulk Move, Bulk Delete, Batch Cat, and Category Merge WordPress Plugins. If you are REALLY daring, try the MySQL query to search posts and change categories on the WordPress Support Forum.

Here are some more articles, resources, and references to help you search and replace in the WordPress database:

• MySQL Manual – UPDATE Syntax
• WP Tricks – Replace Content on Your WordPress using SQL
• 8 Useful WordPress SQL Hacks – Smashing Magazine
• Remove/Replace Content from the WordPress Database – Digging into WordPress
• Search and Replace a Custom Field in WordPress using phpMyAdmin – Brock Angelo
• Quick Search and Replace in MySQL for WordPress Database – Idowebdesign
• 6 Simple Steps to Change Your Table Prefix in WordPress by Tdot
• 11 Useful WordPress SQL Hacks For Maintaining Your Blog – My Guy Solutions
• 8 Useful Tricks To Clean Up & Streamline Your WordPress Database – Tutorials, Tips, Tricks
• Eric’s Archived Thoughts: Correcting Corrupted Characters in WordPress
• mySQL Database Search & Replace With Serialized PHP – David Coveney
• KrazyWorks – MySQL Global Search and Replace Script

This post originally appeared on WordCast and is used with the permission of WordCast and the author to republish here. Updated 2014.

---

Subscribe Copyright Lorelle VanFossen.