Useful MySQL Queries with WordPress

Writing MySQL queries for Word Press can be helpful for mass updates, visualizing large amounts of data, or creating exports (just to name a few things). But WordPress has a database structure that can be tricky to work with, requiring more than just a simple join to combine post data and its accompanying post meta in a useful, easy to digest way. Here are some queries I use often.

Querying wp_posts

So let’s start with a simple one. Let’s get some posts!

SELECT * FROM wp_posts;

This is as basic as they get and will return a bunch of usually not overly useful information about your posts. So let’s see a query that is a bit more… well… useful. Let’s say you need a list of all your custom post types.

SELECT DISTINCT post_type FROM wp_posts;

Now you can have quick reference to how your custom posts are named, which IS useful. Or am I the only one who always forgets how exactly I named custom post types. Are they singular or plural? Have dashes or underscores? Why have I still not standardized my approach to these things?!

Querying wp_postmeta

Most of the time when I’m querying the database, it’s really the post meta I’m after. One way to get it is a brute force approach:

SELECT * FROM wp_posts p LEFT JOIN wp_postmeta m on p.ID = m.post_id WHERE p.post_type = 'post';

This will get you all of the post meta you’re looking for, but also a whole lot of columns you’re probably not. Not to mention you’ll have a row for each meta value you need, which is annoying. What if you want to run an export of some posts and with some meta values as their own column? Well then you need to do a bit more joining.

SELECT
     p.ID,
     p.post_title,
     m1.meta_value as 'Some Meta Value',
     m2.meta_value as 'Some Other Meta Value'
FROM 
     wp_posts p
LEFT JOIN 
     wp_postmeta m1 on p.ID = m1.post_id AND m1.meta_key = 'some_key'
LEFT JOIN
     wp_postmeta m2 on p.ID = m2.post_id AND m2.meta_key = 'some_other_key'
WHERE
     p.post_type = 'some_custom_post';

Now we’re getting somewhere. This returns some results that we could actually export to csv and give to a client, or use to make some sort of analysis or something.

Querying Taxonomies

Querying Terms and Taxonomies in WordPress can get a bit confusing; at least for me it does. Let’s do a more complicated example. Let’s say you need a list of all the terms (categories, tags, custom taxonomies) associated to each post of a specific custom post type. What would that query look like?

SELECT
    p.ID as ID,
    p.post_title as Title,
    GROUP_CONCAT(
        wp_terms.name
        ORDER BY
            wp_terms.name ASC SEPARATOR ', '
    ) AS Terms
FROM
    wp_posts p
    LEFT JOIN wp_term_relationships t ON p.ID = t.object_id
    LEFT JOIN wp_terms ON t.term_taxonomy_id = wp_terms.term_id
WHERE
    p.post_type = 'some post type'
GROUP BY
    p.ID

So, as you can see this is a bit more involved. You need to get the Term ID by joining the posts table to the term_relationships table. Then you join the term_taxonomy table to the terms table via the term_taxonomy_id. And then you can use a GROUP_CONCAT to put them all into a comma separated list.

I hope you found these SQL queries for WordPress helpful and you should play around with these in your own database… just be sure to back it up first if you start playing with update queries. Despite the fact that WordPress has many built in functions to get the data you need, it is still helpful to know how to use SQL to extract the data you need and understand the WordPress database structure.