Clean Tables / WP CLI | Search Replace

/ Wordpress Cheat Sheet, SprUng, wordpress - more cheats / 13 minutes of reading

More from this category



Queries to remove unused / old plugins tables

Count and remove orphaned postmeta data from wp_postmeta table:

Get Count:

[code style=”php”] SELECT COUNT(pm.meta_id) as row_count FROM wp_postmeta pm LEFT JOIN wp_posts wp ON = pm.post_id WHERE IS NULL; [/code]

Delete Records:

[code style=”php”] DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON = pm.post_id WHERE IS NULL; [/code]

Count and remove orphaned commentmeta table fro wp_commentmeta table:

Get Count:

[code style=”php”] SELECT COUNT(cm.meta_id) as row_count FROM wp_commentmeta cm LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL; [/code]

Delete Records:

[code style=”php”] DELETE cm FROM wp_commentmeta cm LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL; [/code]

Count and remove orphaned taxonomy relationships from wp_term_relationships

Get Count:

[code style=”php”] SELECT COUNT(tr.object_id) as row_count FROM wp_term_relationships tr INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE tt.taxonomy != “link_category” AND tr.object_id NOT IN (SELECT ID FROM wp_posts); [/code]

Delete Records:

[code style=”php”] DELETE tr FROM wp_term_relationships tr INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE tt.taxonomy != “link_category” AND tr.object_id NOT IN (SELECT ID FROM wp_posts); [/code]

Moving WordPress sites can be a pain in the posterior. Code has to be moved, databases have to be moved, and those are the easy parts. If you are actually moving it to a new domain, or sub-domain, things get really tricky. Thankfully, there is a tool to help. Let’s take a look at wp-cli search-replace.

If you aren’t familiar with it yet, WP-CLI is the WordPress developer’s swiss army knife. That is if you could add new tools to an ever expanding swiss army knife and then still fit it in your pocket. WP-CLI is a command line tool for WordPress developers to help with everything from setting up to create a new plugin, to performing basic database operation.

One of the features that comes stock with WP-CLI is the search-replace command. As the title suggests, this is a way to search and replace things in your database. Things like domain names or URLS. This is the easiest example for most developers to understand but it can also be used to replace image names, or to help move your images to a CDN if you are handing it manually.

When moving your site from your development environment to your production environment, you may need a command like this.

wp search-replace ‘http://example.dev’ ‘http://example.com’

Not sure if you got the parameters just right? add the –dry-run switch and WP-CLI will show you what it is going to do, but not actually make any of the changes.

The beautiful things about using WP-CLI’s search-replace command over say a simple SQL statement is that WP-CLI knows to scan your entire database, and WP-CLI knows how to unpack JSON payloads stored in your DB, perform the search and replace on them, and then if changed, re-pack them and update the database.

This is only one of the many things that WP-CLI can do for WordPress developers but it is probably the one most used. When you have your WordPress site setup on Pantheon, you know that moving your site from development to test or production is just a click of a button. Behind the scenes though, one of the steps we do is run WP-CLI’s search-replace to change all the instances of your development URL to either your test or production URL.

Check out WP-CLI today and watch how it changes how you approach admin tasks for WordPress.

wp search-replace

Searches through all rows in a selection of tables and replaces appearances of the first string with the second string.

By default, the command uses tables registered to the $wpdb object. On multisite, this will just be the tables for the current site unless --network is specified.

Search/replace intelligently handles PHP serialized data, and does not change primary key values.

OPTIONS

<old>
A string to search for within the database.
<new>
Replace instances of the first string with this new string.
[<table>…]
List of database tables to restrict the replacement to. Wildcards are supported, e.g. 'wp_*options' or 'wp_post*'.
[–dry-run]
Run the entire search/replace operation and show report, but don’t save changes to the database.
[–network]
Search/replace through all the tables registered to $wpdb in a multisite install.
[–all-tables-with-prefix]
Enable replacement on any tables that match the table prefix even if not registered on $wpdb.
[–all-tables]
Enable replacement on ALL tables in the database, regardless of the prefix, and even if not registered on $wpdb. Overrides –network and –all-tables-with-prefix.
[–export[=<file>]]
Write transformed data as SQL file instead of saving replacements to the database. If <file> is not supplied, will output to STDOUT.
[–export_insert_size=<rows>]
Define number of rows in single INSERT statement when doing SQL export. You might want to change this depending on your database configuration (e.g. if you need to do fewer queries). Default: 50
[–skip-tables=<tables>]
Do not perform the replacement on specific tables. Use commas to specify multiple tables. Wildcards are supported, e.g. 'wp_*options' or 'wp_post*'.
[–skip-columns=<columns>]
Do not perform the replacement on specific columns. Use commas to specify multiple columns.
[–include-columns=<columns>]
Perform the replacement on specific columns. Use commas to specify multiple columns.
[–precise]
Force the use of PHP (instead of SQL) which is more thorough, but slower.
[–recurse-objects]
Enable recursing into objects to replace strings. Defaults to true; pass –no-recurse-objects to disable.
[–verbose]
Prints rows to the console as they’re updated.
[–regex]
Runs the search using a regular expression (without delimiters). Warning: search-replace will take about 15-20x longer when using –regex.
[–regex-flags=<regex-flags>]
Pass PCRE modifiers to regex search-replace (e.g. ‘i’ for case-insensitivity).
[–regex-delimiter=<regex-delimiter>]
The delimiter to use for the regex. It must be escaped if it appears in the search string. The default value is the result of chr(1).
[–regex-limit=<regex-limit>]
The maximum possible replacements for the regex per row (or per unserialized data bit per row). Defaults to -1 (no limit).
[–format=<format>]
Render output in a particular format.
—
default: table
options:
– table
– count
—
[–report]
Produce report. Defaults to true.
[–report-changed-only]
Report changed fields only. Defaults to false, unless logging, when it defaults to true.
[–log[=<file>]]
Log the items changed. If <file> is not supplied or is “-“, will output to STDOUT. Warning: causes a significant slow down, similar or worse to enabling –precise or –regex.
[–before_context=<num>]
For logging, number of characters to display before the old match and the new replacement. Default 40. Ignored if not logging.
[–after_context=<num>]
For logging, number of characters to display after the old match and the new replacement. Default 40. Ignored if not logging.

# Search and replace but skip one column
$ wp search-replace 'http://example.test' 'http://example.com' --skip-columns=guid

# Run search/replace operation but dont save in database
$ wp search-replace 'foo' 'bar' wp_posts wp_postmeta wp_terms --dry-run

# Run case-insensitive regex search/replace operation (slow)
$ wp search-replace '[foo id="([0-9]+)"' '[bar id="1"' --regex --regex-flags='i'

# Turn your production multisite database into a local dev database
$ wp search-replace --url=example.com example.com example.test 'wp_*options' wp_blogs

# Search/replace to a SQL file without transforming the database
$ wp search-replace foo bar --export=database.sql

# Bash script: Search/replace production to development url (multisite compatible)
#!/bin/bash
if $(wp --url=http://example.com core is-installed --network); then
    wp search-replace --url=http://example.com 'http://example.com' 'http://example.test' --recurse-objects --network --skip-columns=guid --skip-tables=wp_users
else
    wp search-replace 'http://example.com' 'http://example.test' --recurse-objects --skip-columns=guid --skip-tables=wp_users
fi

These global parameters have the same behavior across all commands and affect how WP-CLI interacts with WordPress.

Argument Description
--path=<path> Path to the WordPress files.
--url=<url> Pretend request came from given URL. In multisite, this argument is how the target site is specified.
--ssh=[<scheme>:][<user>@]<host|container>[:<port>][<path>] Perform operation against a remote server over SSH (or a container using scheme of “docker”, “docker-compose”, “vagrant”).
--http=<http> Perform operation against a remote WordPress installation over HTTP.
--user=<id|login|email> Set the WordPress user.
--skip-plugins[=<plugins>] Skip loading all plugins, or a comma-separated list of plugins. Note: mu-plugins are still loaded.
--skip-themes[=<themes>] Skip loading all themes, or a comma-separated list of themes.
--skip-packages Skip loading all installed packages.
--require=<path> Load PHP file before running the command (may be used more than once).
--[no-]color Whether to colorize the output.
--debug[=<group>] Show all PHP errors and add verbosity to WP-CLI output. Built-in groups include: bootstrap, commandfactory, and help.
--prompt[=<assoc>] Prompt the user to enter values for all command arguments, or a subset specified as comma-separated values.
--quiet Suppress informational messages.

Command documentation is regenerated at every release. To add or update an example, please submit a pull request against the corresponding part of the codebase.

https://premium.wpmudev.org/blog/replacing-image-links/

After installing an SSL certificate on your site to amp up your security, you may notice your images suddenly don’t display at all. Don’t panic – just read on for the fix.

When you install an SSL certificate, like the free one from Let’s Encrypt we wrote about recently, your site’s address adjusts slightly and even a slight adjustment means some elements on your site will probably break. Your site sees a conflict with the URL where it’s programmed to run and the old URL some content such as images are using. Everything has moved to your https URL, but your images still point to http.

This means you need to change all the image links and replace http with https. What a pain in the rear, right? Fortunately, you don’t have to track down and edit every single image you have uploaded. You can make the switch manually in your database or you can opt for the easier and safer route with a plugin.

If you have successfully updated your site with your new secure URL, but the images aren’t all functioning properly, read on to see how you can manually make the changes you need in your database or view the services and plugins you can use instead which are safer and quicker to use.

Manual Search and Replace with Queries

It’s possible to change your image links manually in your database, though, be forewarned: It can be easy to make a mistake and further break your site. While I’ll still show you how you can make these changes for completeness, it’s important to make note of the risks and it’s up to you to decide whether you should proceed.

I recommend you use a trusted plugin instead since there’s a bit less of a chance to make a mistake. Still, you should use the right amount of caution when making any changes to your site.

It’s a good idea to backup your site completely before making any changes such as editing your site’s image links. To get details on how to do this, check out some of our other posts such as How to Backup Your WordPress Website (and Multisite) Using Snapshot and 7 Top Premium and Freemium WordPress Backup Plugins Reviewed.

That being said, if you find yourself in a rare situation where you need to take a look at your database and tables to sniff out some broken image links, you can do this with a database search and replace query.

Log into phpMyAdmin and click on your site’s database that’s listed on the left, then click the Query tab. In the SQL query on database box toward the bottom of the page, enter the following query, the click Go:

‘

[code style=”php”] UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, ‘src=”http://www.your-site.com’,%20’src=”https://www.your-site.com’); [/code]

Be sure to update www.your-site.com to reflect your real domain name and change the wp table prefix of wp_posts if you don’t use the default and have previously changed it. If you’re using Multisite, be sure to also run this query afterward if you need to update images there:

Replace the 2 in wp_2_posts to match the correct site ID as it corresponds to your subsites. You need to run this query for each of the sites in your network. This many not always be necessary, but if your network is set to use sub-directories and your sites aren’t mapped to use different domains, you may find you need to change your image links.

You also need to run the query below to update the GUID for images that are set as attachments:

[code style=”php”]UPDATE wp_2_posts SET `post_content` = REPLACE (`post_content`, ‘src=”http://www.your-site.com’,%20’src=”https://www.your-site.com’);
[/code]

Similar to the first example, you need to replace the domain with your actual URL and change the table prefix if you don’t use the default setting. You may also need to run this query for all the sites in your network:

[code style=”php”]UPDATE wp_posts SET `guid` = REPLACE (`guid`, ‘http://www.your-site.com’, ‘https://www.your-site.com’) WHERE post_type = ‘attachment’;
[/code]

[code style=”php”]
UPDATE wp_2_posts SET `guid` = REPLACE (`guid`, ‘http://www.your-site.com’, ‘https://www.your-site.com’) WHERE post_type = ‘attachment’;
[/code]

Don’t forget to replace the 2 in wp_2_posts with the actual site ID number for the sites in your network.

Your images should all be updated to reflect your new URL using https. If it doesn’t work, sometimes trying again does the trick. It also helps to run each query one at a time, rather than attempting to run them all at once.

Using a plugin to search for and replace all the images on your site is a lot easier than doing this manually. It’s also a lot less likely that you would make a mistake that could ruin your site. Still, it’s important to double check everything you type in since you could still make a mistake.

These plugins are regularly updated so you don’t have to worry about being left behind with an outdated plugin. While not all of these plugins were intended for use on Multisite, they should still work when activated on a site-by-site basis. No matter which option you choose, they’re all quality plugins that should work well for your sites.

Better Search Replace

Better Search Replace is one of the plugins on this list that specifically has Multisite support. It has a simple design and not a lot of features or settings, but at the same time, it’s the only options most people ever need.

All you need is a running installation of WordPress and you can use this plugin which incidentally is as easy to install as it is to use. The best feature of this plugin is that you’re able to do a test run to see what tables are affected by the options you choose before you actually apply the changes. This means you’re less likely to make a mistake which makes it a valuable tool to have in your toolbox.

Interested in Better Search Replace?

Better Search Replace

The Search and Replace plugin is able to handle a lot more than just replacing image links on your site. You can also use it to change your site’s URL and domain, backup and restore your database, and change your table prefixes from the default wp.

This plugin also has the ability to do a dry run to test out your proposed changes before pushing them through. It can be easier to avoid mistakes when you can see the results of your selections before having to worry about undoing your last changes.

Although, there is one requirement that this plugin needs to run which is a PHP version of 5.4 or newer. Search and Replace does install like most other plugins and there aren’t any extra steps involved. It also includes Multisite support.

WP Migrate DB is normally used for migration your site, but you can also use it to search for and replace old image links. It’s also great for migrating a locally developed site to a live setup.

It’s easy to install and doesn’t have any special requirements to run. While many wouldn’t necessarily think of this plugin initially when wanting to replace image links, it works great and it’s the first choice for this task for many WordPress users.

Wrapping Up

Images can be tricky business when switching your domain to include an SSL certificate. You can update your siteurl all you want, but those pesky images are still going to stay broken. The only solution to fix this issue is to update the image links yourself, although, it should be a breeze with the plugins in this post.

For other broken links on your site that aren’t images, there’s a special fix for this as well, which you can check out in our post How to Fix (Or Remove) Broken Links on Your WordPress Website.

Have you been able to successfully change your image links? What plugin did you use or which one is your favorite? Do you have one you would rather use instead? Share your experience in the comments below.

Scroll to Top