7 WordPress Database Queries To Search Your Blog For Anything
Running 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.
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.
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.
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.
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.
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.
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.
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!)
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.
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.
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.
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