Would you like to know how you can search and replace in phpMyAdmin?
The URL, image, or even the text on your website may need to be changed occasionally or replaced with new content. If this is the case, you do not need to search for them on your website since doing so would take too much time.
To achieve this, you need to install the appropriate plugins. Additionally, you can utilize the phpMyAdmin search and replace it in the entire database.
The purpose of this article is to explain how you can use phpMyAdmin's search and replace functionality.
What is the Search and Replace feature in phpMyAdmin?
The search and replace function in phpMyAdmin allows you to look for a word in your database and replace it with a different word.
The method is beneficial if you need to replace many words in one query. In phpMyAdmin, you need to run an “update TABLE_NAME set FIELD_NAME” query.
Here are a few possibilities to consider. Suppose you use a link text image or anything else regular on your website.
After some time, however, you may wish to make a change to the text or image of a link to improve your SEO, for example. One of the first things that comes to mind is reviewing each post and page individually.
The accuracy of your work and the amount of time you dedicate may result in the loss of some text, images, or links, regardless of how careful you are. Therefore, it is more efficient to use a specific query in phpMyAdmin or a plugin instead.
How to Search and Replace in phpMyAdmin?
As mentioned above, you may be able to change queries on the database without using a plugin by using phpMyAdmin search and replace.
Follow these steps to do this:
- Log-in to your cPanel.
- From the database section, click on phpMyAdmin.
- Click on your website’s database in the phpMyAdmin; a new window pops up.
- Click on SQL from the toolbar in the new window.
- Type the following query in the exact format:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'Text to find', 'text to replace with');
As an example, you may wish to use search and replace within a specific post in order to change a certain word.
In this case, you will need to use the code above and replace the codes as follows:
update wp_posts set post_content = replace(post_content,'Text to find','text to replace with');
As you may say, in the query you need to replace ‘text to find’ with the word you wish to replace. Then replace ‘text to replace with’ with the word or sentence you would like to change your specific word to.
- Click on Go to run the query.
Finally, a message appears on the screen once the process is complete indicating the number of words that have been replaced.
Search and Replace WordPress Database with Plugins
Aside from using phpMyAdmin find and replace, which is a direct way to perform the replacement, you can also make use of WordPress search and replace plugins that are designed to perform the replacement within a WordPress database.
Here are some of these plugins discussed in more detail:
Better Search Replace plugin
There are several excellent find-and-replace plugins available, but Better Search Replace stands out among them.
Follow these steps to use the plugin:
- Download and install the plugin from the official WordPress website.
- Activate the plugin.
- Go to your WordPress Dashboard.
- Go to Tools.
- Click on the Better Search Replace plugin.
When you click on Better Search Replace in your WordPress tools, you can see a few tabs include Help, Settings, and Search/Replace.
- Type in the word you want it to search in the Search for tab.
- Type in the word you want it to replace it with in the Replace with tab.
- In the Select Tables section, select the tables that the plugin performs on.
By doing this, you will be able to search through your database and replace words, links, etc.
- Enable Run as dry run to view the available words before you actually begin the process.
It is important to note that enabling this option will prevent the entire search and replace process from being undertaken so then you must disable it to run the process.
- Disable Run as dry run after you have received the report.
- Click on the Run Search/Replace button.
Search & Replace Plugin
Another plugin that allows you to search and replace is the Search & Replace plugin. After installing and activating this plugin, follow these steps to use it:
- Go to your WordPress Dashboard.
- Go to Tools.
- Click on the Search & Replace plugin.
- Go to Search & Replace tab.
- Type in the word you want it to search in the Search for box.
- Type in the word you want it to replace it with in the Replace with box.
- In the Select tables section, select the tables that the plugin performs on.
- Enable Dry Run to view the available words before you actually begin the process.
By enabling this option, you will prevent the entire search and replace process from taking place. Therefore, after viewing the available words, you must disable it in order to accomplish the search and replace.
- Disable Dry Run after you have received the report.
- Click on the Do Search & Replace button.
Manually Search and Replace a URL in WordPress Database
There is a concern on the part of users about following the phpMyAdmin search and replace for WordPress URL method. Generally speaking, two types of URLs can be searched and replaced.
- Your permalink may have been changed. If WordPress permalinks do not work, you must replace the URLs in the menus, widgets, and posts with new ones. There is a possibility that a few URLs will be missed if you change them manually.
- It may be necessary for you to correct the wrong URLs for the videos and images. In the event that you change the image address, you may also need to make adjustments to your code to re-display the image.
It is essential to keep in mind that URLs are stored in separate tables within your database.
For this reason, if you wish to manually alter these URLs, you must look for them in every single table that you want to modify.
These URLs can be found in the following list:
- Inside posts and pages: “posts_content” field in the “wp_posts” table
- The old link manager: “link_url” and “link_image” fields in the “wp_links” table
- URLs of Custom Menu items: “meta_value” field in the “wp_postmeta” table
- Options or themes and plugins: “option_value” field in the “wp_options” table
- URLs inside comments: “comment_content” fields in the “wp_comments” table
This is an example related to wp_posts table:
Manually changing your URLs will take a great deal of time and effort. The reason for this is that you will have to open each table individually and locate the URLs you wish to replace. Therefore, it is recommended that you use a plugin to minimize the possibility of errors.
Use MySQL Scripts to Find and Replace URLs in the Database
MySQL scripts are another option for searching and replacing old URLs in the database. The find and replace in MySQL database method is very useful if you are running a large and complex website.
It is very easy to replace old URLs with new ones using this MySQL search and replace script:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find string', 'replace string');
Searching and replacing old URLs in your database tables is accomplished with this command.
Follow these steps to change the URL of all your tables using the phpMyAdmin replace string in all tables:
- Log into your cPanel and then open phpMyAdmin.
- Select your website's database.
- Click on the SQL in the toolbar.
- Type in the following code in the box:
UPDATE wp_options SET option_value = replace(option_value, 'Existing URL', 'New URL') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET post_content = replace(post_content, 'Existing URL', 'New URL'); UPDATE wp_postmeta SET meta_value = replace(meta_value,'Existing URL','New URL'); UPDATE wp_usermeta SET meta_value = replace(meta_value, 'Existing URL','New URL'); UPDATE wp_links SET link_url = replace(link_url, 'Existing URL','New URL'); UPDATE wp_comments SET comment_content = replace(comment_content , 'Existing URL','New URL');
Changing the URLs of images and posts in your website requires the use of the following codes:
For images in a single post:
UPDATE wp_posts SET post_content = replace(post_content, 'Existing URL', 'New URL');
For attached images in a post:
UPDATE wp_posts SET guid = replace(guid, 'Existing URL','New URL');
For images with old link manager:
UPDATE wp_links SET link_image = replace(link_image, 'Existing URL','New URL');
Finally, after using the correct SQL command in the SQL window, ensure that you click on “GO” in order to begin the process. Your old URLs will eventually be replaced by your newly chosen ones.
This article described how you can use phpMyAdmin's search and replace functionality. Various methods are presented to use this feature.
This article is intended to provide you with useful information.