One of the many reasons why we like WordPress is the power and flexibility that the publishing platform provides. It offers so many customi...
One of the many reasons why we like WordPress is the power and flexibility that the publishing platform provides. It offers so many customizations, and you can do so much more with it. One of the best features about it is, you can use and interact with WordPress Databases to add your own functionality, and extract some extra data that you can't normally do without installing some third party plugins. For those of our readers who're WordPress users, we'd like to share some simple (and some advanced) MySQL database queries you can run to do some advanced and useful actions.
How to run MySQL queries?
Most web hosting providers will give you access to your cPanel, where you can access your web files, mail, domains, ftp accounts, and so on. Among the many tools, you will see phpMyAdmin, a tool used to access MySQL databases. Open phpMyAdmin, and go to the Databases tab, where you'll see all the databases present on your server. Your WordPress database could be the one named after your domain name, or it might have a 'wp' or 'wrdp' prefix/suffix. Click on the database to see individual tables.
Now, click on the SQL tab. You can run your database queries from this interface. Just copy/paste a query, and hit Go, and you'll see the results right in front of you!
Useful WordPress Database Queries
1. Extracting Emails left by commentators
The WordPress comments data is stored in a table named 'wp_comments'. With each new comment made on a post on your blog, a new entry is added into the table. You can run a simple database query to extract the emails left by commentators.
SELECT DISTINCT comment_author_email
FROM wp_comments;
The DISTINCT is there to remove duplicates. And instead of extracting author email, you can also extract 'comment_author_url', 'comment_author_ip', etc.
You can use such a query to, for example, send a thank you email to all these addresses for contributing to the discussion on your blog. Of course, most of them might be fake, but still, worth a shot, eh?
2. Extract all posts by an author
Each author on your site has a specific ID. You can find this ID by opening a user's profile, and observing the URL. Once you know the ID, you can use that to run a database query to extract all posts by that author.
SELECT ID, post_title
FROM wp_posts
WHERE post_status = 'publish'
AND post_author = 11;
This query fetches the post id, and the post title of all posts. Additional values you can extract are 'post_date', 'post_content', 'guid' etc.
3. Delete Unused Tags
You might accumulate a bunch of empty tags you never used over time. You can do a clean up by looking for unused tags using the following query.
SELECT name, slug
FROM wp_termsWHERE term_id
IN (
SELECT term_id
FROM wp_term_taxonomy
WHERE taxonomy='post_tag'
AND count='0'
);
4. Searching for content
You can use queries to search your posts for a specific keyword, or HTML tag! All you have to do is, modify the bold text given in the query below.
SELECT ID, POST_TITLE
FROM wp_posts
WHERE post_content LIKE '%your_search_term_or_tag%'
AND post_status = 'publish';
You can also run a search inside your comments by using wp_comments instead of wp_posts.
5. Searching posts by date
You can modify the date range given below to anything you want, and search for posts between that date range. For example, the following query searches for all posts written in the month of July.
SELECT ID, POST_TITLE
FROM 'wp_posts'
WHERE 'post_type' = 'post'
AND 'post_date' > '2013-06-30 23:59:00'
AND 'post_date'< '2013-08-01 00:01:00';
Make sure that you get the date syntax right. You can also combine this query with #2 to get all posts by a certain author in the month of July, for example.
These were some of the very basic queries. Did you like them? Please leave your responses in the comments section below. And if you want, we could show you some of the more advanced queries that can you a whole new level of power over your blog! So, what do you say?
COMMENTS