Custom Queries in WordPress Tutorial

April 13, 2015
by Inaam Hussain

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,

Wordpress database structure

WordPress 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.

SQL Query
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.

SQL Query
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.

SQL Query
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“.

SQL Query
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“.

SQL Query
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.

SQL 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.
 

About

Engineer. Blogger. I like to be updated on new upcoming emerging technologies.

Free PDF

Subscribe and get AngularJS Tips PDF. We never spam!
First Name:
Email:

1 comment

  1. |

    I’m looking for how to do this via Visual Composer. To be specific, I need to events via a specific term_id to only grab a particular category of events via The Events Calendar plugin. Any suggestions would be greatly appreciated! dgfrench at g mail dot com

Leave a Comment