Search & Replace Entries In the WordPress Database

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.

Was this helpful?

What’s your goal today?

1. Sign up to our newsletter and keep up to date with the latest news. Our team consist of highly skilled ex-sysadmins and hosting veterans, ready to share their unique hosting-related knowledge with you.

Sign up for ScalaHosting monthly News Letter and we will keep you up to date about the most valuable pieces of content we publish on our blog

2. Explore our VPS Hosting - with our 30-day unconditional money-back guarantee and free, effortless migration, you can try our award-winning Managed Cloud VPS solutions risk-free.

Rado
Author

Working in the web hosting industry for over 13 years, Rado has inevitably got some insight into the industry. A digital marketer by education, Rado is always putting himself in the client's shoes, trying to see what's best for THEM first. A man of the fine detail, you can often find him spending 10+ minutes wondering over a missing comma or slightly skewed design.

Write a Comment

Required*

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.