WordPress database search: WP DB search with WP-CLI

How to use WP-CLI

To use WP-CLI on your site, you will need to find and then use the SSH credentials from the site’s portal. To use WP-CLI, you will need to use a command-line application that will depend on if you are using a Mac or PC. If using a Mac, you can use Terminal. If you are using a PC, then the recommendation would be to use Putty.

WP-CLI can only be used from the valid WordPress installation, which would be from:

cd public_html

If you are not in a proper WordPress installation, then WP-CLI will fail to use it. In addition, if a critical error is being caused on the site, then it will also cause WP-CLI not to work correctly.

If you need to search through all of the tables in the database using this WP-CLI command:

wp db search ‘foo’ –all-tables

If you need to be able to search through all of the tables in the site’s database that match the match the prefix that is set in the site’s wp-config.php file, then use this WP-CLI command:

wp db search ‘foo’ –all-tables-with-prefix

Searching all network tables

If you need to be able to search all of the database tables on a multisite network, then you can use this WP-CLI command:

wp db search ‘foo’ –network

Searching a specific table

If you want to target a specific database table, then you can set that in the WP-CLI command:

wp db search ‘foo’ wp_posts

Searching multiple tables

You can extend the WP-CLI command to cover multiple database tables (example targets the posts and comments database tables) using:

wp db search ‘foo’ wp_posts wp_comments

If you wanted to search even more database tables in this example, we are going to search posts, postmeta, comments, and commentmeta tables, and then you can include those in the WP-CLI command:

wp db search ‘foo’ wp_posts wp_postmeta wp_comments wp_commentmeta

Outputting statistics (“Stats”) when searching

You can output stats on the WP-CLI search command using the stats flag:

wp db search ‘my search string’ –stats

Output only matches

To be the able only output matches only, you can use the following WP-CLI:

wp db search ‘foo’ wp_posts –matches_only

Using regex to search

WP-CLI search command also supports using regex (regular expressions):

wp db search ‘^wp.*’ –regex

Using regex and case insensitivity

You can make the regex search case-insensitive, including a flag WP-CLI:

wp db search ‘^wp.*’ –regex –regex-flags=i

WP-CLI provides the power to run many complex searches or tasks but all from the command line and using commands that make sense and are easy to follow if you have used the command line before. Common tasks are always worth running through WP-CLI since they never have the delayed issues trying to run specific tasks in wp-admin (WordPress dashboard).

If you need to search through a multisite database at a specific subsite in a multisite, you could search through the domain string using this WP-CLI command:

wp db search example.com –url=example.com/foo

Power in using WP-CLI

The WP-CLI database search command is potent and makes it very easy to search strings and for specific references in your site’s database all from the command line. The command includes a number of flags and the global parameters that it supports.