MySQL Query to Retrieve Category from wp_posts

by

Using this query to retrieve all of my posts with a specific post_type

SELECT *
FROM wp_posts
WHERE post_type="product";

As described here the category is not in the wp_posts table but in term tables wp_terms wp_term_relationships wp_term_taxonomy

Searching through all the tables for a category I had in mind, the only instance of a category I could find was in the wp_terms table which contains the following columns

  • term_id
  • name
  • slug
  • term_group

Looking for cross-references to this in other tables and somehow relate them back to wp_posts is posing some complications.

My thinking is term_id I should be looking for as it seems like a foreign key, but the only instance of that is in wp_term_taxonomy, and the only information I can find in the table related to my category (or rather term_id) is

  • term_taxonomy_id
  • term_id
  • taxonomy
  • description
  • parent
  • count

So the only information I can get from this is letting me know that my term_id taxonomy is a product_cat and in count tells me how many posts I have for this particular category.

Knowing a little bit about MySQL I know if there’s any hope of doing this I need to modify my query and do a JOIN or two.

But I’m only finding very limited information on what exactly I can latch onto.

Here’s the structure of wp_posts

 `wp_posts` (
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
 `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_content` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `post_title` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `post_excerpt` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `post_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
 `comment_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
 `ping_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
 `post_password` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 `post_name` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 `to_ping` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `pinged` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_content_filtered` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
 `guid` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 `menu_order` int(11) NOT NULL DEFAULT '0',
 `post_type` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
 `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
 `comment_count` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 KEY `post_name` (`post_name`(191)),
 KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
 KEY `post_parent` (`post_parent`),
 KEY `post_author` (`post_author`)
 )

Is it even possible to modify my query to only retrieve wp_posts table rows for specific categories?

4 s
4

Figured it out. @belinus is probably the solution for you if you’re looking to do this within WordPress.

As for just a raw SQL query you can play around with, I found this one, modified it a little bit and it returns all the products for a particular category.

There are three tables required to do this wp_posts wp_term_relationships and wp_term_taxonomy

SELECT *
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.term_id IN (307)
GROUP BY wp_posts.ID

Just replace 307 with the term_id of the category you’re looking to get results for. You can find this by searching in the wp_terms name column and it will return the term id associated.

You can also return multiple categories if you wish, just comma seperate them in the WHERE clause for example WHERE wp_term_taxonomy.term_id IN (307, 450, 200, 99).

Leave a Comment