7 WordPress Database Queries To Search Your Blog For Anything

cardfiles   7 Wordpress Database Queries To Search Your Blog For AnythingRunning a WordPress blog or a website isn’t really that much of a big deal at the beginning. It’s actually pretty simple. You install WordPress on a webserver, you upload and install a theme, start blogging and you’ve got your website up and running. For the first two years, it’s a piece of cake. You may not have a successful blog from day one, but you know what – if you’re a good writer, the traffic starts arriving. Before you know it, you have a decent little following.

You may not be a pro blogger from day one, but with enough determination, dogged persistence and good old elbow grease, you can make it there. However, the road to that summit of success is a rough one. You will discover that eventually your traffic surpasses the capacity of your shared web hosting. Then, you will learn that the free newsletter service you signed up for can’t handle the audience load you’ve accumulated.

And then, there are the blog posts themselves. What started out as a few dozen articles eventually turns into hundreds. And then after a few years, you’ve got well over one to two thousand articles, and your site is growing exponentially. You can’t even remember those early articles anymore. Your site might have evolved over the years, and the structure of your articles has changed, but those early articles haven’t. Maybe some links got broken. Maybe some images disappeared. The fact is, unless you know how to query your WordPress blog database, then you may not see everything that’s going on with your site, and you may not be able to properly manage your blog.

Thankfully, most web hosts give you access to your database through phpMyAdmin, and the following WordPress database queries can help you keep better tabs on all of your thousands of posts.

Monitoring Your Posts With WordPress Database Queries

The following tips on using phpMyAdmin to query your WordPress database is not intended to do anything invasive or dangerous. These are essentially queries that you can use to look for specific posts in your blog that may be buried within years of entries that would take forever to find, when you WordPress search tool just isn’t effective enough.

In some cases, as I’ll describe below, you may want to do things like find all of the spam comments on your blog so you can delete them, find unused tags, find every single article written by a particular author or inside of a particular category, and much more. Once you understand how to structure these queries, it becomes an extremely valuable tool to dig data out of your huge blog.

Running phpMyAdmin SQL Queries

When you go to your web host and access the phpMyAdmin tool, you’ll see all of your databases available in the left menu. Click on your WordPress database, and you’ll see all of the WordPress tables that are available for you to query data from.

wpquery1   7 Wordpress Database Queries To Search Your Blog For Anything

To run the queries that I’m going to describe in this article, just click on the SQL tab, and you’ll see a field where you can paste the queries. You can copy them right out of this article and paste them into your own query field if you like.

wpquery2   7 Wordpress Database Queries To Search Your Blog For Anything

So, now that you’re set up to roll your own queries, let’s get rolling!

Extract Email Addresses Left by Commenters

Over the years, you’ll get a whole lot of comments from your readers, and most of those readers will provide their email address when they post to your blog. Many bloggers don’t even give this a second thought, but if you consider it, your blog has built one of the most valuable forms of marketing available to any business – a mailing list. ¬†You can extract all of those email addresses from the comments on your blog by running a simple query:

SELECT DISTINCT comment_author_email FROM wp_comments;

The resulting list will provide you with every single email address that you never even knew you had. In my case, that amounted to over 2,000 email addresses.

wpquery3   7 Wordpress Database Queries To Search Your Blog For Anything

Given, a lot of those (most, probably) will be fake email addresses that are worthless to you anyway, but there’s a good portion of that list made up of legitimate email addresses for readers that took enough of an interest in your blog to post a comment. Consider sending a marketing email thanking those people for commenting on your site, and recommending they sign up for your newsletter to get regular updates about the latest stories added to your blog.

Identify and Delete Unused Tags

Over the years, you may also develop a bunch of tags your site that are unused. It’s always a good idea to clean up unused tags, and you can do this quickly with a couple of quick SQL statements. The first is to identify the list of tags using this query:

SELECT name, slug 
FROM wp_terms
WHERE term_id
IN (
SELECT term_id
FROM wp_term_taxonomy
WHERE taxonomy='post_tag'
AND count='0'
);

The results from this query will look something like the results below. In my case, I discovered 22 unused tags on my blog that I need to clean up or apply to legitimate posts.

wpquery4   7 Wordpress Database Queries To Search Your Blog For Anything

You could use the “Delete” links to delete individual tag entries from the database, or you could run the following DELETE statement to delete them all in bulk (use with extreme caution and only after taking a database backup!)

DELETE FROM wp_terms
WHERE term_id
IN (
SELECT term_id
FROM wp_term_taxonomy
WHERE taxonomy='post_tag'
AND count='0'
);

This will clean up all of those unused tags in one fell swoop.

Find Posts by Author

You can also quickly extract a list of posts by a specific author. First, go into your WordPress panel and go to the users section. Click on the users’s name and on the user page URL, you should be able to find a “user_id=xx” section. Make note of the ID number.

wpquery5   7 Wordpress Database Queries To Search Your Blog For Anything

Use that ID in the following query to look up those posts. In the example below, I’m searching for posts written by Mark Dorr (user #11) on my blog.

SELECT ID, post_title
FROM wp_posts
WHERE post_status = 'publish'
AND post_author = 11;

The results are the ID and title for every post written by that author.

wpquery6   7 Wordpress Database Queries To Search Your Blog For Anything

If you have a huge blog, you may actually want to add a LIMIT=1000 or something into the query, or you can apply a date range search to the query (see below).

Search For Content Inside of Posts and Comments

Another cool thing you can do with WordPress DB queries is sifting through your post content for specific text or even HTML markup. For example, let’s say you want to find every single post where you’ve inserted a picture using “img src=xx”. You can use SQL wildcard symbols to ask for any words or sentences using this technique. Here’s what the query looks like.

SELECT ID, POST_TITLE
FROM wp_posts
WHERE post_content LIKE '%src=%'
AND post_status = 'publish'
LIMIT 100;

I’ve added the “LIMIT 100″ bit at the end because I’d have well over 1000 posts with that code in it.

wpquery7   7 Wordpress Database Queries To Search Your Blog For Anything

By the way, you can also do this sort of wildcard search through your WordPress comments as well. This is a really cool way to find comments where people tried to slip through HTML links or image links for example. I tried the search on my blog and found a comment that I didn’t even know about (and promptly deleted!)

wpquery8   7 Wordpress Database Queries To Search Your Blog For Anything

Search for Posts by Date

You can also search through your blog posts by date range by using a query that checks the “post_date” and compares it to specific dates at the upper and lower range.

SELECT ID, POST_TITLE
FROM 'wp_posts'
WHERE 'post_type' = 'post'
AND 'post_date' > '2009-04-15 08:00:00'
AND 'post_date'< '2009-04-30 08:00:00'
LIMIT 100;

Here are what the results look like.

wpquery10   7 Wordpress Database Queries To Search Your Blog For Anything

I actually use this query a whole lot, because when combined with the author query or the text-search query, it provides a far more powerful search functionality than any regular search tool I’ve used. The results are fast and accurate.

Advanced Search Queries

Of course, folks that are very capable with SQL calls know that the above queries are extremely basic. You can get very advanced with some of these searches, such as this query that allows you to show the post ID, author and title of your last 1000 posts, sorted by date.

SELECT p.id as post_id, u.user_nicename as author, p.post_title, p.post_name as post_slug, p.post_date as local_publish_date, p.comment_count 
FROM wp_posts p, wp_users u 
WHERE p.post_status='publish' 
AND p.post_type='post' 
AND u.id = p.post_author 
ORDER BY p.post_date DESC 
LIMIT 500;

A query like this connects the authors actual name to the user ID in the wp_posts table. In combining data from different tables, you can make the final output for more useful. Now you can see not only your post recently published posts, but also the author names, and also the count of comments for each post.

wpquery11   7 Wordpress Database Queries To Search Your Blog For Anything

Best of all, with every one of the queries that I’ve detailed in this post, you can just click on the “Export” link at the bottom of the output table to send the data to an output format of your choice, such as a CSV file.

wpquery12   7 Wordpress Database Queries To Search Your Blog For Anything

As you can see, going straight to your WordPress database to search for content buried inside of your blog can be wicked effective. Using creative SQL queries, you can look up all sorts of details about the posts – like a comparison of post counts by author, a count of posts about a certain topic, a list of blog comments where someone has typed their email address, and much more. You just can’t do this kind of stuff from the WordPress panel.

Do you know any creative WordPress database queries for a WordPress database? Is this the first you’ve ever heard of doing this? Share your own thoughts and experiences with this technique in the comments section below!

Image Credits: Database Concept Via Shutterstock

The comments were closed because the article is more than 180 days old.

If you have any questions related to what's mentioned in the article or need help with any computer issue, ask it on MakeUseOf Answers—We and our community will be more than happy to help.

2 Comments -

0 votes

ValueCoders

Thanks Ryan for sharing amazing WordPress queries. Beneficial for WordPress bloggers.

-Mike

0 votes

James Bruce

I really like the ones for finding unique emails from comments and deleting unused tags – awesome tips, thanks Ryan.