A WordPress website can accumulate a vast amount of data very quickly, especially if you develop it actively and attract significant traffic. Usually, this isn’t a problem.
The issues begin when you need to make changes to that data. Finding and editing a database entry isn’t necessarily that hard, but if you need to modify lines across different tables, doing it manually will take a lot of time.
That’s why people have been looking for ways to search and replace entries in the WordPress database. Today, we’ll explore some of the most popular methods.
WordPress Search & Replace – What Is It And Why Do You Need It?
Your WordPress website relies heavily on its database. It stores everything from the login credentials for the WP dashboard, through posts, pages, and comments, to the customized configuration of your theme and your plugin settings.
Your site’s URL is also stored in the database. WordPress needs it to render the site correctly. Unlike other content management systems like Joomla that let you access the website and develop it via the server’s IP, WordPress won’t work unless the domain is resolving correctly.
So, there’s a lot of information, and every now and again, you might need to edit it for various reasons. Here are some of them.
You need to change the site’s domain name
This is one of the most common uses of the search and replace operation in WordPress. Your site’s domain name is entered in several places inside the database. Manually editing all of them is time-consuming and tedious, and if you forget to go over even a single entry, the entire site’s functionality and appearance could be broken.
You are switching from HTTP to HTTPS
In this day and age, it’s advisable to obtain an SSL certificate and provide your users with a secure connection as soon as you launch the website. Nevertheless, if you haven’t done so initially, you can later use search and replace to edit the entries in the database and switch to the HTTPS protocol. This way, you will not only change the website URL but also update any hardcoded links, ensuring that visitors always have a secure connection to your site.
You’re rebranding your business
Changing the name of your online project could be a massive undertaking even without going through months of content and swapping the old name for a new one. A simple search and replace operation can take a lot of the burden.
You need to fix grammatical mistakes or change the style guide
Excellent grammar and a characteristic style can make a great impression on your site’s visitors. Unfortunately, combing through and systemizing months’ or years’ worth of content can be an absolute nightmare. The search and replace function can speed things up significantly.
How to Perform Search & Replace on Your WordPress Database
There are several different methods for performing search and replace on your database. However before we get to them, it might not be a bad idea to point out that any changes to your database can completely break your website. Always double-check your search and replace terms before you press Enter, and don’t forget to back up your website before you modify database entries.
With that out of the way, you can take a look at the different options. Let’s start with the most user-friendly one.
Search & Replace with a plugin
As always, you can find plugins that can fully automate the task for you. With over 1 million active installations, Better Search Replace is by far the most popular one. After installing and activating it, you’ll find it under the Tools menu.
The interface is pretty easy to use. The Search for and Replace with fields are self-explanatory, and below them, you can choose which tables you’d like to search through. You can also disable case sensitivity, exclude GUIDs from your searches, and you have a dry run option, which shows you the search results without changing anything.
Better Search Replace appears to be intuitive and reliable, and the decent ratings suggest that plenty of people are happy with the results it provides. Nevertheless, there are plenty of other plugins offering similar functionality. If you have the time and a staging environment where you can test them, it pays to have a look at them and see which one fits your requirements the best.
Search & Replace using a PHP script
In addition to plugins, you can also find PHP scripts that automate the process of modifying database entries. The most popular one is Search Replace DB by UK-based software development outfit interconnect.
The script is available for free, but before you can get the download link, you need to fill in a small form, which, in addition to your personal details, also asks you to acknowledge the risks associated with using the tool. Let’s see what these risks are.
The script arrives as a ZIP file. You need to extract the data from it and upload it to a folder inside your website’s document root directory (if you host the site under the account’s main domain, it will be in public_html). By default, the script’s folder is named Search-Replace-DB-master, but you can rename it any way you want. For our example, we’ll call it db-script.
Your next step is to open a browser and navigate to the script’s folder. In our case, we need to go to www.domain.com/db-script.
You can now see the options. Once again, the interface is pretty intuitive. You can search through specific tables and columns and replace the entries you need. Because interconnect’s script is not WordPress-specific, you have a few other options for different types of databases and encoding variations. Most of you can leave these settings in their default state.
You get a test run option, and if you’re sure you want to continue, you can make the changes with a single click.
Before you can search and replace your database, however, you need to enter the database’s name and the login credentials for the MySQL user. In the past, this information used to be pulled automatically from the configuration files, but the developers removed the functionality because of the security risk of leaving the sensitive information public.
Even after the changes, it’s essential to delete the script as soon as you’re ready with the changes. Otherwise, the hackers will have a convenient platform for launching a brute-force attack against your password.
Search & Replace with WP-CLI
People comfortable working with the command line will likely use the search & replace functionality embedded into WP-CLI. WP-CLI is a powerful tool that enables you to manage your WordPress installation via an SSH connection. It’s been around for a while now, and it’s pre-installed on pretty much any hosting plan you can think of.
To edit database entries with it, you first need to establish an SSH connection and navigate to your site’s document root folder. The search and replace command itself has a fairly straightforward syntax. Here’s how it should look:
wp search-replace ‘[your search string]’ ‘[the string you want to replace it with]’
For example, if you want to change your domain from domain.com to example.com, you’ll need to enter the following:
wp search-replace ‘domain.com’ ‘example.com’
WP-CLI will show you a list of all the changes it has made.
If you’re not quite sure, you can add –dry-run to see the search results without making changes. There are other options for searching through individual tables, excluding tables from the results, adding the replacement string as a new entry, etc.
Replace database entries via phpMyAdmin
The final method for replacing database entries we’ll show you today is via phpMyAdmin – an open-source database management platform integrated into most web hosting control panels. You need to bear in mind, however, that phpMyAdmin won’t scan the entire database for matches.
You can swap one value with another, but you’ll need to specify the targeted table and column. To do that, open phpMyAdmin and select your site’s database from the menu on the right.
Click the SQL tab and enter the following query:
update [the name of the table] set [the name of the column] = replace([the name of the column], ‘the string you’re searching for]’, ‘the string you want to replace it with]’);
If we assume that we want to change “domain.com” to “example.com” in the options_value column in the wp_options table, we’ll need the following string:
update wp_options set option_value = replace(option_value, ‘domain.com’, ‘example.com’);
Using the Simulate query button, you’ll see how many entries phpMyAdmin has found without making any changes to the database. If you press Go, phpMyAdmin will replace the requested entries.
