hay friends now we discus about wordpress database Queries,this post for all WordPress user you all like this.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.
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_emailThe DISTINCT is there to remove duplicates. And instead of extracting author email, you can also extract 'comment_author_url', 'comment_author_ip', etc.
FROM wp_comments;
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_titleThis query fetches the post id, and the post title of all posts. Additional values you can extract are 'post_date', 'post_content', 'guid' etc.
FROM wp_posts
WHERE post_status = 'publish'
AND post_author = 11;
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_TITLEYou can also run a search inside your comments by using wp_comments instead of wp_posts.
FROM wp_posts
WHERE post_content LIKE '%your_search_term_or_tag%'
AND post_status = 'publish';
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_TITLEMake 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.
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';
These were some of the very basic queries. Did you like them? Please leave your responses in the comments section below.
