• Home
  • Technology
    • Ebullientech
    • Entrepreneurship
    • Video Technology
    • Cloud Computing
    • Business Analysis
  • Thoughts
    • Food
    • General
    • Stories
    • Places
    • Workplace
    • Social Issues
    • Reviews
  • Quotes
  • Ebullientech News and Events
  • About

Kamalika's Notebook

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

You are here: Home / Kamalika / Technology / Ebullientech / How To Get Posts Category In Wordpress

How To Get Posts Category In WordPress

April 30, 2015 By Kamalika Leave a Comment

Problem

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

Published Date
Author
Category
Post Title
Blog URL

Solution

[bash]

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;

[/bash]

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: Ebullientech, MySQL, WordPress Tagged With: Custom WordPress Queries, Ebullientech, Ebullientech Interactive LLP, SQL Query, WordPress, WordPress Consultant Mumbai, WordPress Database Diagram, WordPress Database Structure, WordPress Developer Mumbai, WordPress SQL Query Category, WordPress to SQL Query Query

« HTTP Status Codes
My days in Mumbai… the awakening! »

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Connect with Facebook

Let’s connect…

  • Facebook
  • Instagram
  • LinkedIn
  • Twitter
  • YouTube

Hello There…

I'm Kamalika, a techpreneur & startup mentor, blogger, hobbyist photographer, Netflix & Kindle indulgent, food connoisseur, Starbucks aficionado and former Disney employee ...next

Looking for something?

Tags

All in One SEO Pack AWS Banaras Blogger Blogging Blogs Creating Backup Schedule in RHEL 5 Dasaswamedh Ghat Ebullientech Ebullientech Interactive LLP Facebook Facebook Developers GeoTech GeoTech Informatics HipHop for PHP iPhone iPhone Apps Kamalika Guha Roy Kashi Ganga LAMP MindTwister MySQL Nabaneeta Guha Roy Online Marketing PayPal PayPal's suspension of Indian bank transactions Pentago PHP PHP & MySQL Saraswati Puja Search Engine Optimization SEO The Ganges TypePad UCO HUT UTV Indiagames Varanasi Web 2.0 Web 2.0 Design Web Design Web Marketing Website Design WordPress WordPress.COM WordPress.ORG

Timeline

  • 2020 (7)
  • 2019 (4)
  • 2018 (1)
  • 2015 (6)
  • 2014 (13)
  • 2013 (9)
  • 2010 (7)
  • 2009 (2)

Topics

Copyright © 2021 · Kamalika Guha Roy, Powered by Ebullientech Interactive LLP