Custom Queries in WordPress Tutorial

In this tutorial we will learn to write custom MySql queries to fetch blog data. Custom quiries might be required for a number of reasons for eg. if you are writing a word-press plugin which requires data to be fetched in a certain way or if you are creating JSON REST API to be consumed by some other application for eg a mobile app. We will also learn about the word-press database structure.

First will see below Database structure,

 

Will covered following MySql queries,

• List of All Published Post Data
• List of Categories
• Fetching blog posts based on category.
• Featured image of particular Post
• Authors Bio
• Single Post based on Post id

 

List of All Published post data

See below query for fetch all Published post data.


SELECT *
FROM wp_posts
WHERE post_status = "publish" and post_type = "post"
ORDER BY post_date DESC


This MySql query will fetch all published post with descending date order. Means latest post comes first.

All main post data is present in wp_posts table.

 

List of Categories

See below query for fetch list of categories which is used for posts.


SELECT wp_terms.name
FROM wp_term_taxonomy
LEFT JOIN wp_terms
ON wp_term_taxonomy.term_id=wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy="category" and wp_term_taxonomy.count != 0
ORDER BY wp_terms.name ASC


Categories data is mainly stored in two tables that is “wp_terms” and “wp_term_taxonomy“.

You will find Category name in “wp_terms” table and taxonomy category stored in “wp_term_taxonomy” table. So based on term_id in both table using left join we can easily fetch name of categories.

 

Fetching blog posts based on category.

Fetching posts based on category is a bit tricky.Because here we have to use three queries in while loops.

So, lets see how it’s working.


Query1 : SELECT wp_terms.name,wp_terms.term_id,wp_term_taxonomy.term_taxonomy_id
FROM wp_term_taxonomy
LEFT JOIN wp_terms
ON wp_term_taxonomy.term_id=wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy="category" and wp_term_taxonomy.count != 0
ORDER BY wp_terms.name ASC
while ($row)) {
Query2 : SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id=$row['term_taxonomy_id']
while ($row1) {
Query3 : SELECT *
FROM wp_posts
WHERE ID=$row1['object_id'] AND post_status='publish'
while ($row2) {
...Data of post in $row2
$Category[] = $row2;
}
$post[$row['name']] = $Category;
}
$Category=[];
}


In above scenario you will get basic idea how three query will execute in while loops.

First Query will get all categories and it’s taxonomy id and this ID will pass to 2nd query and that will fetch object Id from wp_term_relationships table. and object id will used as a post id, so we can easily get all Post based on that.

so how will store and show data,
in $post[$row[‘name’]] array we are storing particular category and in that we are adding $Category that is category wise data.
so at the end will get data in $post.

 

Get Featured image of particular post

See below query to fetch a particular post’s featured image.

So, First we will need to find Post ID of the post and then we will pass that ID to the below query in place of “ID of post“.


SELECT wp_posts.guid
FROM wp_posts
LEFT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.meta_value
WHERE wp_postmeta.post_id="ID of post" AND wp_postmeta.meta_key='_thumbnail_id'


So here we are using two tables for getting featured image url (i.e: “wp_posts” and “wp_postmeta“). In wp_posts table we have actual image placed URL and in wp_postmetawe have meta_key that is ‘_thumbnail_id‘. So applying LEFT JOIN based in post id that will return featured image URL.

 

Authors Bio

See below query to fetch Authors Name of written post.

So, First will need to find authors id from wp_posts table for particular post and then that author id will be passes to below query in place of “Author ID“.


SELECT display_name
FROM wp_users
WHERE ID="Author ID" LIMIT 1


Actual name is placed in wp_users tables so based on id we can easily find author name and other details.

 

Single post based on post id

Finding single post is easy just need to find particular “POST ID” of post which you need to show. See below query.


SELECT *
FROM wp_posts
WHERE ID="POST ID" AND post_status = "publish" AND post_type = "post"


Even there are many more mysql queries is there for wordpress.
Comment below if you have any in your mind and then will update in post.