• Skip to primary navigation
  • Skip to main content
Sal Ferrarello
  • About Sal Ferrarello
  • Speaking
  • Connect
    Mastodon GitHub Twitter (inactive)
You are here: Home / Programming / Search WordPress Post and Post Meta
Screenshot of wp find-content command being run to find a specific Gravity Form shortcode

Search WordPress Post and Post Meta

Last updated on January 11, 2018 by Sal Ferrarello

Recently, I had to find instances of where specific Gravity Forms were being used. This came with multiple challenges.

Challenges

  • Searching for gravityform id="7" returns results for just gravityform (i.e. too many results)
  • If the gravity form appears in post meta (rather than the primary content), it is excluded (i.e. missing results)

SQL Solution

Find Gravity Form Shortcode in Post Content

This code returns a list of posts (ID and post_title) that contain Gravity Form 7.

SELECT 
    `ID`, 
    `post_title`
FROM `wp_posts` 
WHERE 
    `post_content` LIKE '%[gravityform id="7"%' 
    AND `post_name` NOT LIKE '%-revision-%'
    AND `post_name` NOT LIKE '%-autosave-%';

Find Gravity Form Shortcode in Post Meta

This code returns a list of posts (ID and post_title) that contain Gravity Form 7 in their post meta.

SELECT 
    `wp_postmeta`.`post_id`,
    `wp_posts`.`post_title`
FROM `wp_postmeta`
    LEFT JOIN `wp_posts`
    ON `wp_postmeta`.`post_id`=`wp_posts`.`ID`
WHERE
    `wp_postmeta`.`meta_value` LIKE '%[gravityform id="7"%'
    AND `wp_posts`.`post_name` NOT LIKE '%-revision-%'
    AND `wp_posts`.`post_name` NOT LIKE '%-autosave-%';

WP CLI Solution

Manually wrangling these two separate SQL statements is not very efficient, so I wrote a custom WP CLI command to do this for me. Originally, the command was going to search for a specific Gravity Form shortcut but I realized it was easy to generalize the code to search for any string.

You can find my WP CLI command find-content on GitHub and it can be installed with the command

 wp package install git@github.com:salcode/wp-cli-find-content.git

Example Usage and Output

$ wp find-content '[gravityform id="7"'
+----+-----------------------------+----------+
| ID | permalink                   | location |
+---+------------------------------+----------+
| 1  | http://wp.test/hello-world  | content  |
| 8  | http://wp.test/display-meta | postmeta |
+----+-----------------------------+----------+

Geeky Details

Details – Find Gravity Form Shortcode in Post Content

My first SQL query was

SELECT 
    `ID`, 
    `post_title`
FROM `wp_posts` 
WHERE 
    `post_content` LIKE '%[gravityform id="7"%';

this seemed to work well until I noticed there were too many results.

The problem was I was including revisions and autosaves. The post_name of a revision or autosave looks like the following respectively, 8-revision-v1 and 8-autosave-v1 (where 8 is the original post_id and v1 indicates it is the first version). This allowed me to add some additional WHERE clauses to exclude these posts.

SELECT 
    `ID`, 
    `post_title`
FROM `wp_posts` 
WHERE 
    `post_content` LIKE '%[gravityform id="7"%' 
    AND `post_name` NOT LIKE '%-revision-%'
    AND `post_name` NOT LIKE '%-autosave-%';

This gave me just the posts I want. There were still two drawbacks:

  • This only lists posts where the shortcode appears in the post content
  • I don’t have the URL (permalink) for the post

I address finding posts where the shortcode appears in the post meta in the next section.

I don’t have a solution for adding the permalink to this SQL query but I did add it to the WP CLI command I wrote.

Details – Find Gravity Form Shortcode in Post Meta

While I was able to find the Gravity Form Shortcode in the Post Meta with some relatively straight forward SQL

SELECT `post_id`
FROM `wp_postmeta`
WHERE
    `meta_value` LIKE '%[gravityform id="7"%';

this does not address eliminating revisions and autosaves. Since this SQL only queries the wp_postmeta table (and you can only determine if a post is a revision or autosave by querying the wp_posts table), I needed to do an SQL join, specifically a LEFT JOIN. If you’re not familiar with a LEFT JOIN (a.k.a. LEFT OUTER JOIN), I find this Coding Horror: A Visual Explanation of SQL Joins a good place to start.

I rewrote my query to use both tables

SELECT `post_id`
FROM `wp_postmeta`
    LEFT JOIN `wp_posts`
    ON `post_id`=`ID`
WHERE
    `meta_value` LIKE '%[gravityform id="7"%';

which let me then refactor further and exclude revisions and autosaves.

SELECT `wp_postmeta`.`post_id`
FROM `wp_postmeta`
    LEFT JOIN `wp_posts`
    ON `wp_postmeta`.`post_id`=`wp_posts`.`ID`
WHERE
    `wp_postmeta`.`meta_value` LIKE '%[gravityform id="7"%'
    AND `wp_posts`.`post_name` NOT LIKE '%-revision-%'
    AND `wp_posts`.`post_name` NOT LIKE '%-autosave-%';

Details – WP CLI

Rather than wrangling these two separate SQL queries and manually merging the results, I was excited to create my first custom WP CLI command. I look forward to automating more of the work I do. See WP CLI command find-content on GitHub.

Sal Ferrarello
Sal Ferrarello (@salcode)
Sal is a PHP developer with a focus on the WordPress platform. He is a conference speaker with a background including Piano Player, Radio DJ, Magician/Juggler, Beach Photographer, and High School Math Teacher. Sal can be found professionally at WebDevStudios, where he works as a senior backend engineer.

Share this post:

Share on TwitterShare on FacebookShare on LinkedInShare on EmailShare on Reddit

Filed Under: Programming, Solution Tagged With: command line, MySQL, WordPress, wp-cli

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Copyright © 2023 · Bootstrap4 Genesis on Genesis Framework · WordPress · Log in