WordPress is easy to use content management system.But there are times writing SQL queries is need of hour.I have faced many situations which required writing WordPress queries.I am listing most of the queries in this post.I hope you all will find these WordPress queries useful.I will be updating this post with new queries so stay tuned.
You can use these queries using PhpMyAdmin or from MySQL dashboard.The same queries will work fine on both the platforms.I have written separate tutorial on PhpMyAdmin.If you are not comfortable using these queries then you should read that tutorial first.For details on using PhpMyAdmin to run the query read this tutorial.You can export output generated by running SQL on PhpMyAdmin in different formats like CSV.
Get ID of all category and tags
WordPress posts are organized using Category and tags.Each category and tag have a unique numeric identification number (ID) assigned to them.As we will see below many queries need category id and tag id.So this query is base of all those queries.I will be covering those queries below as well.
SELECT distinct(e.term_id),distinct(e.name) FROM wp_terms e ;
Above query gives you name of all category and tags along with their ids.You can copy the query and run it using PhpMyAdmin.
Get all posts belonging to a category or tag
We will use information fetched in previous query into this query.This is one of the most common tasks performed by me.Recently I wanted to organize content on my site.For this purpose I wanted to list all posts belonging to a category or tags.This requirement forced me to use below query
SELECT distinct(post_title),e.name FROM wp_posts a LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id ) LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id ) LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) LEFT JOIN wp_terms e ON ( e.term_id = d.term_id ) WHERE e.term_id > 0;
The above query gives list of posts in one column and category names and tag names in second column.In above query you can replace the last line with below code to get posts belonging to a specific category or tag.You will have to replace the text id of category or post with numeric id you got from first query of this post.
WHERE e.term_id = id of the category or tag
Number of posts under category and tag
This information is displayed on WordPress dashboard under Posts ->Categories and Posts->Tags.But it is tough to import the information.So I use query to get list of posts under different categories or tags.
select wp_terms.term_id,wp_terms.name,count from wp_terms,wp_term_taxonomy where wp_terms.term_id = wp_term_taxonomy.term_id;
This single query will give you names of tags and categories in one column,id of categories and tags in second column and third column will give you number of posts under each of them.
Disable and Enable Comments
Comments are integral part of any blog.They make it interactive and increase interest of visitors.Sometimes visitors end up reading comment area more interestingly.But I have seen many blogs disabling comments on posts older than certain time period.Everyone has different reasons to disable them on their posts but I have tried to cover most of the situation.
Disable on all posts
update wp_posts set comment_status = 'closed' where post_type = 'post'
Disable on all pages
update wp_posts set comment_status = 'closed' where post_type = 'page'
Enable on all posts
update wp_posts set comment_status = 'open' where post_type = 'post'
Enable on all pages
update wp_posts set comment_status = 'open' where post_type = 'page'
Disable on posts older than certain date
update wp_posts set comment_status = 'closed' where post_type = 'post' and post_date < 'YYYY-MM-DD'
for example you want to disable comments on post published before 2011-01-01 (year 2011) then you should replace YYYY-MM-DD with this date.
Enable on posts older than certain date
update wp_posts set comment_status = 'open' where post_type = 'post' and post_date < 'YYYY-MM-DD'
Changing WordPress Username and Passwords
If you are facing below problems with WordPress then this query will help you
- Installed WordPress with admin username and want to change it
- While installing WordPress forgot to give admin password and now unable to login
- Change admin password
- Change user name of any user
- change password for any user
Get User Names and IDs
select id,user_nicename,display_name from wp_users
Above query will give you names of all users on your website.This will also give ids of them.Remember id of a user is unique.We will be using this id in next query.
Change passwords of users
update wp_users set user_pass=md5("your-password") where ID=N
Change display names of users
update wp_users set display_name="your-name" where ID=N
Get posts from a author
If you have multi user blog in place then these queries might prove very handy.This will give list of all posts written by different users of your blog.
select post_title,user_nicename from wp_posts,wp_users where wp_posts.post_author = wp_users.id
Above query will return list of posts in one column and user name in other column.You can filter the result set easily to get list of posts by each author.Also if you want to get only posts written by a user from above query you can enter the user id (the numeric value) in place in wp_users.id in above query.
Get list of comment author emails of a post
I have tested this query but not have used in practice.But it is worth sharing this query here.You can use this query for creating an Email campaign list.A person commenting on an article shows interest in that topic.Lets assume you have written a new article on that topic.
You can create an email marketing list by taking email of all people commenting on related article.You can send email to all of them informing about the new article.Lets understand a situation this method is worth applying.
You have written a post on written test of any exam.Many people have commented on that article.Now after sometime you write an article on Interview of same exam.It is worth informing people who commented on written test article about this new post.
Lets see the query
SELECT DISTINCT (comment_author_email), post_title FROM wp_posts, wp_comments WHERE wp_posts.id = wp_comments.comment_post_ID
Above query will give unique email id’s on one column and post name corresponding to that in second column.You can filter the data on post name to get list of all unique email ids.You can also filter on email ids to see if a particular user has commented on more than one post.
Get Word Count of all posts
It is good to test your website for thin content.Most SEO plugins (including WordPress SEO by Yoast for WordPress) recommend 300 words as minimum word count.So it is better idea to get list of all posts along with number of words.You can then rework on posts with less content.
SELECT post_title, LENGTH( post_content ) - LENGTH( REPLACE( post_content, ' ', '' ) ) +1 FROM wp_posts
This query will give you post name in one column and number of words content in body of the post in second column.You can sort the result on number of posts to get post with low number of words.You can then focus on rewriting the post and increase content for better search engine ranking and providing in depth knowledge to visitors.
Consider sharing the post in case you found this useful.