WordPress is great, but let’s face it – most good plugins are born out of a need to do something WordPress doesn’t do out of the box. If you’re a WordPress developer, you work with all kinds of websites big and small. Each have their own share of issues, whether it’s a new site from the ground up, a redesign, or a new functionality or break/fix assignment.
I was just telling a developer co-worker today that sometimes you find the best things completely by accident. Looking for yet another “solution to a problem” for a WordPress website migration, I found a new plugin that I didn’t know existed. It got me thinking about all the little tools I have for search and replace and bulk editing of data in WordPress – and yet another blog post was born (lol).
What we’re going to talk about today are different ways to search, find, replace, and bulk edit content and data in WordPress. This is yet another post you’ll want to bookmark, because you’ll run into one or more of these issues in the future – and need to refer back to this page for the solution.
WordPress Find and Replace Migration Script
Website Migration Domain Name Find and Replace in WordPress
Many WordPress developer work and code live on website builds (I’ve done my share). Others work in a more structured environment using a repository like SVN and a relay deployment process involving a developer, QA, and production server. Whether you work live, setup a custom environment, or use MAMP, or XAMPP to do your development – the time comes when you have to set a WordPress project to the final destination URL. Here ensues all kinds of chaos as you need to change the URL in multiple places in the database, as well as preserve image paths in content and widgets (amongst other things). Everyone seems to have their own process for dealing with website migration issues.
A new gem I found just today was the WordPress Search and Replace Tool. This is a PHP script (not a plugin) that can be used to selectively scan your database and find and replace a domain name in post and page content, and even widgets, settings, and options.
Here’s an image from the author’s website:
You’ll see in the image that in the initial screen you can selectively choose which database tables to scan and do a search and replace. The beauty of this script is that it can handle serialized string lengths (often found in widgets settings and options), as well as multi-byte Unicode. Read this article on data serialization in WordPress to better understand what the actual issues are.
If you’re migrating WordPress from a development server to live location, you’ll find this script very, very handy. The author says it also works with Drupal, or just about any CMS system as well. Always be sure to backup your database before performing any search and replace operations.
If you know enough SQL and PHP code to be dangerous, you could even write your own database find/replace script in PHP.
Find and replace with WP Migrate DB Plugin
Reading the comments for the Search and Replace for WordPress script we just talked about, we found some commentators complaining that “it wasn’t a plugin”. Well they were right, and there are a great deal of (non technical) WP website owners that would have trouble installing a script in the root of a website and running it (outside of WordPress).
In that case, you could try to use the WP Migrate DB plugin which offers to export your database as a MySQL data dump, finding and replacing the URL’s so you can save it locally. It does (also) claim to take into account “serialized data”.
Here’s an image of the export screen where you enter the current and new URL, along with file paths.
Find and replace in the WordPress database directly using MySQL and phpMyAdmin
You may have a one-off problem that a bulk script or plugin just can’t solve. These are cases where it would be handy to know some SQL code you can run in phpMyAdmin directly in your web control panel directly modifying your database (ALWAYS backup first!).
Here are some helpful find and replace WordPress SQL queries you can use:
Maybe you have a situation where your client needs to replace a consistently misspelled word, or a brand name. You can search and replace your content areas right in your webhost (cpanel) phpMyAdmin tool using a simple MySQL query this like this:
Using the same format you could do a SQL search and find for Latin and UTF type character replacement:
You could also do a find and replace for a domain name or URL path (in content only) using the same query:
Maybe what you need to search and replace involves some type of code like HTML:
Having said that – what if you need to find how many instances of something exist and if there are multiple iterations? You may not know if the value has a space before, after, etc.
Try a SQL query in phpMyAdmin like this:
Once you find out how many matches there are, you can go back through and build a new UPDATE query:
Sometimes you need to do a find a replace in custom fields or meta values, which is a very similar SQL statement.
This is to replace a meta key:
This is to replace a meta value:
Ever need to bulk delete all your post revisions?
How about bulk editing your pages and making them posts?
Bulk delete posts over so many days old in WordPress? Replace “120” below with the number of days old you want to delete posts from.
Maybe you want to bulk disable comments on posts older than a certain date:
Or bulk delete all spam comments at once:
Or batch delete all comments that are unapproved:
You can even bulk move posts from one author to another:
WordPress Search and Replace Plugin
There are quite a few WordPress search and replace plugins, but Search and replace is one of the most popular with more than 300,000 downloads.
It allows you to search in an ID, post content, GUID, titles, excerpts, metadata, comments, tags, categories, authors, and all kinds of other things.
Here’s an image of the main screen from the author:
WordPress Bulk Editing, Deletion, and Creation Plugins
While the search and replace plugin has an unbelievable amount of options – there does exist quite a few bulk editing plugins for specific use cases.
Bulk Category, Tag, and Custom Field Editing
The Bulk Add Tags plugin allows you to enter a mass amount of tags at once.
Alternately, the WPEssence Bulk Categories allows you to bulk create multiple categories at once, and even define slugs and parents. With the latest version you can also bulk create tags, and even custom taxonomies (anything registered by your theme or plugins).
Here’s a plugin that will bulk edit your custom fields.
WordPress Bulk Media Editing
Once plugin I’ve used a lot is Enable Media Replace. While technically not “bulk editor” – the fact that you can replace an image without having to upload a new one, delete the original (or use FTP) is a godsend. I have however used the Rename Media Files plugin to bulk rename media on many occasions.
You might also enjoy Bulk-select featured image where you can assign featured images from the Media page (instead of post/page edit screen).
If you have a lot of images with EXIF data (dates), you can use the EXIFize My Dates plugin to bulk change / update them as well.
Bulk Creating WordPress Content
Have you ever wanted to setup a lot of pages at once for a new WP website? I give to you Bulk Page Creator. If you need more than pages, with Bulk Content Creator you can bulk create posts, pages, and even custom post types.
Get yourself in a tizzy creating too much content? There’s always the WP Mass Delete plugin.
Bulk Moving Posts to Tag or Category
Maybe you need to bulk edit and move posts from a particular tag or category, use the Bulk Move plugin for that. By the same token, you can use Bulk Delete Posts to get rid of mass posts in tags or categories.
Bulk Resize Images in WordPress
Are your images out of control, with really huge full size versions in your Media center? I can’t count the number of times a client has done this – and the Bulk Resize Media plugin is just what the doctor ordered.
You can optimize your images with EWWW Image Optimizer in Media or NextGen Gallery – great for reducing bandwidth, and increasing page load speed (which is great for SEO!).
Additional WordPress SQL Code Query Resources
Search, replace, and delete code snippets post: nice roundup of dozens of queries
10 Life Saving SQL Queries: awesome post with very useful WP SQL code query snippets
20 Helpful WordPress SQL Queries Snippets: Another batch of really awesome and useful WordPress SQL snippets.
