--> 5 Useful WordPress Database Queries for Important Tasks | Experience Lab - Online business creation and development guide for bloggers and startups

5 Useful WordPress Database Queries for Important Tasks

One of the many reasons why we like WordPress is the power and flexibility that the publishing platform provides. It offers so many customi...

WordPress Database Queries

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!

Running SQL Queries in phpMyAdmin

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.


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.


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?



Affiliate Marketing,12,Announcement,34,Bing,9,Bitcoin,38,blog,7,Blogger Resources,42,Blogger Templates,4,blogger tricks,156,Blogging ethics,70,Blogging tips,198,Bugs and Errors,34,Business,9,Copyright Violation,9,CSS and HTMLTricks,95,Designs,8,drop down menu,7,eBook,12,Email Marketing,7,Events,30,Facebook,30,Facebook tricks,49,Google,157,Google AdSense,42,Google Analytics,7,Google Plus,51,Google Plus Tricks,38,Guest Posts,112,home,2,How To,77,Internet,1,JSON Feeds,25,Kitchen Recipes,2,Label Based Sitemap Themes,1,Make Money Online,108,Marketing,16,MBT Blogger Templates,7,Menus,1,News,146,Pages,1,Posts,10,presentations,15,Responsive,10,Reviews,7,SEO,307,Settings,6,Shortcode,15,Sitemap Themes,1,Social Media,155,Technology,7,Templates,1,Tips,2,Tools,1,Traffic Tips,80,Video,19,Web Designing,62,web hosting,18,Webmaster Tools,97,Widgets,199,wordpress,26,
Experience Lab - Online business creation and development guide for bloggers and startups: 5 Useful WordPress Database Queries for Important Tasks
5 Useful WordPress Database Queries for Important Tasks
Experience Lab - Online business creation and development guide for bloggers and startups
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share. STEP 2: Click the link you shared to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy