Search and replace in phpMyAdmin is a process in which you automatically look for a word in your database and replace it with another. It's extremely useful if you have many words to fix. To find and Replace in phpMyAdmin you need to run an “update TABLE_NAME set FIELD_NAME” query.
Sometimes you may need to change a URL, an image, or even a text on your website or replace them with new content. In this case, you don’t need to look for them on your website, because doing so is time-consuming.
To do so, you just need to install the right plugins. Also, you can use phpMyAdmin search and replace.
In this article, we discuss how you can use phpMyAdmin search and replace.
Table Of Contents
- Table Of Contents
- What is Search and Replace in phpMyAdmin?
- How to Search and Replace in phpMyAdmin
- Search and Replace WordPress Database with Plugins
- Manually Search and Replace a URL in WordPress Database
- Use MySQL Scripts to Find and Replace URLs in the Database
What is Search and Replace in phpMyAdmin?
The most basic question we need to answer is, when do we use find and replace?
First, let us review some possibilities. Imagine you are using a link, text, image or anything regular on your website. But after some time, to better your SEO or any other reason, you may want to change a link, text or an image. The first way that comes to our mind, is to review posts and pages one by one.
No matter how precise you are and how much time you spend on it, you may miss a few texts, images or links. Therefore, using a specific query in phpMyAdmin or even a plugin is a better solution.
How to Search and Replace in phpMyAdmin
As we mentioned above, other than using a plugin, you can use phpMyAdmin search and replace to change queries on the database.
1. First, log-in to your cPanel.
2. From your database section, click on phpMyAdmin.
3. Once you are in phpMyAdmin, click on your website’s database. A new window pops up.
4. In the new window, click on SQL from the toolbar.
5. Now you must type in the query below in the exact format:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'Text to find', 'text to replace with');
For example, you want to use search and replace in a specific post and change a specific word. Then you need to use the code above and replace the codes, for example:
update wp_posts set post_content = replace(post_content,'Text to find','text to replace with');
As you can probably tell, in the query you need to replace ‘text to find’ with the word you would like to replace. Then replace ‘text to replace with’ with the word or sentence you want to change your specific word to.
6. Finally, click on “Go” to run your query. Once the process is finished, a message pops up on your screen, displaying how many words had been replaced.
Search and Replace WordPress Database with Plugins
Other than using phpMyAdmin search and replace which is a direct way, you can also use plugins designed for replacing in WordPress database. Below, we discuss some of these plugins.
Better Search Replace plugin
Better Search Replace plugin is one of the best find and replace plugins.
To use this plugin, first, you need to download and install it from the official WordPress website and activate it.
After activating you need to head over to your WordPress Dashboard > Tools > Better Search Replace.
Once you click on Better Search Replace in your WordPress tools, you see a few tabs (Help, Settings, and Search/Replace).
The first tab is search/replace. In this page, in the “Search for” option, type in the word you want it to search. Then right below you can see a “Replace with”, you need type in the word you want it to replace it with.
In the “Select Table” section, you can limit the tables that the plugin performs on. This will search for and replace words, links and etc. in your database.
Finally, by enabling “Run as dry run” you can view a report of available words before actually starting the whole process.
Keep in mind, enabling this option prevents the whole search and replace process. In order to use the plugin, you should disable run as dry run after you have received the report and run the process again.
Search and Replace plugin
Search and Replace is another plugin you can use to search and replace. After installing and activating this plugin, head over to Tools to configure it.
In the Search & Replace page, you can replace any query.
Just like the plugin above, you only need to search for the word you would like to change and replace. Finally, click on “Do Replace”.
Manually Search and Replace a URL in WordPress Database
Users are worried to follow this method. Generally, you may search and replace two types of URL:
- You may have changed permalinks. To fix WordPress permalinks not working you need to replace old URLs that are in menus, widgets, and posts. By manually changing them, you may miss a few URLs.
- You may want to correct the wrong video and image URLs. If you change the image address, you may also need to correct your codes to display the image again.
Keep in mind, URLs are stored in different tables of your database. Therefore, if you wish to manually change these URLs you need to look for them in every single table. Below, we show you a list where you can find these URLs:
- 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
If you wish to manually change your URLs, there is a long way ahead of you. Because you need to open tables one by one and look for the URLs you want to replace. Which is why we recommend using a plugin to avoid any mistakes.
Use MySQL Scripts to Find and Replace URLs in the Database
Another way to search and replace old URLs in the database is by using MySQL scripts. This method is very useful if you are running a big and complicated website.
By using this MySQL script you can easily replace old URLs with new ones:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find string’, ‘replace string’);
This command is used to search and replace old URLs in your database tables. If you wish to change every URL in your tables, first open your cPanel and open phpMyAdmin.
Select your website’s database. From the toolbar click on SQL and type in the code below:
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');
If in your website, you have images on your posts, to change their URLs you need to use the code below:
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 right command in the SQL window, make sure you click on “GO” to start the process. Ultimately, your old URLs will be replaced with the ones you have chosen.