• Home
  • Knowledgebase
    • Technology
      • PHP
      • MySQL
      • WordPress
      • Cloud Computing
  • Thoughts
    • General
    • Stories
    • Places
    • Workplace
    • Social Issues
    • Reviews
  • Quotes
  • About
  • Contact

Kamalika's Notebook

thinking nothing...............!!

How To Get Posts Category In WordPress

April 30, 2015 By Kamalika

Problem

Can we get the list of blog posts in the following structure?

Published Date
Author
Category
Post Title
Blog URL

Solution


SELECT p.ID,DATE(p.post_date) Date,u.display_name Author,p.post_title Title,GROUP_CONCAT(t.name) Category,
CONCAT('http://yourdomain.com/blog/',p.post_name) URL
FROM wp_posts p
LEFT JOIN wp_users u ON p.post_author = u.ID
LEFT JOIN wp_term_relationships rel ON rel.object_id = p.ID
LEFT JOIN wp_term_taxonomy tax ON (tax.term_taxonomy_id = rel.term_taxonomy_id AND tax.taxonomy='category')
LEFT JOIN wp_terms t ON (t.term_id = tax.term_id AND t.name!='uncategorized')
WHERE p.post_status = 'publish' and p.post_type='post'
GROUP BY p.ID
ORDER BY Date;

Tables used in the above query are:

wp_posts

wp_posts table contains all kinds of contents of an WordPress Website / Blog and the content types include the following:

  • posts
  • pages
  • custom post types
  • attachments
  • links
  • navigation menu items (which are stored as individual posts)

wp_users

wp_users table contains user data.

wp_term_relationships

WordPress content types are attached to the following types of data:

  • categories
  • tags
  • custom taxonomies and terms
  • post metadata

wp_term_relationships contains the relationship between WordPress contents and categories / tags.

wp_term_taxonomy

wp_term_taxonomy contains the taxonomy details.

wp_terms

wp_terms contains the details of all defined categories and tags.

The WordPress Database Structure

WordPress uses a number of database tables with relationships between them, most of these relationships are one-to-many.

One-to-many relationships occur when each record in TableA may have many linked records in TableB but each record in TableB may have only one corresponding record in TableA.

In case of WordPress, one user can have many posts that they have written related to their user record.

The diagram below is taken from the WordPress Codex and shows the database tables and how they are related / linked:

WordPress Database Diagram

Filed Under: MySQL, WordPress Tagged With: Custom WordPress Queries, SQL Query, WordPress, WordPress Consultant Mumbai, WordPress Database Diagram, WordPress Database Structure, WordPress Developer Mumbai, WordPress SQL Query Category, WordPress to SQL Query Query, WordPress Trainer Mumbai, WordPress Tutor Mumbai, WP Query

Connect with me online

  • Facebook
  • Google+
  • Instagram
  • Linkedin
  • Twitter
  • YouTube

Follow my notebook

Enter your email address to follow my notebook and receive notifications of new posts by email.

A Little About Me

Technology Freak. Educator. Mentor. Digital Marketing Enthusiast. Amateur Photographer. Avid Reader. Music Lover. Movie Buff. Foodie. Lover of WordPress. Ex Disney Employee. For more, follow me at @kamalika. Read More…

Looking for something?

Copyright © 2014 · Kamalika Guha Roy