MySQL Query to Retrieve Category from wp_posts

Question

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?

Answer 1

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

Answer 2

This can be done using the WP_Query class. If all you're looking for is to grab all posts based on the category, you can do that using the ID or slug.

$args = array(
   'cat' => 1,
);

$new_query = new WP_Query( $args );

OR

$args = array(
   'cat_name' => 'news',
);

$new_query = new WP_Query( $args );

From there you write the loop as normal with one exception:

<?php if ( $new_query->have_posts() ) : ?>
   <?php while ( $new_query->have_posts() ) : $new_query->the_post(); ?>
      // Post Code Goes Here
   <?php endwhile; ?>

   <?php wp_reset_postdata(); ?>

<?php else : ?>
    // No Posts Found
<?php endif; ??

You need to include the wp_reset_postdata(); function call which will reset the global $post; back to the main query's value.

Answer 3

I created a query, you can use this to find all the categories related to

SELECT *  FROM wpra_term_relationships 
INNER JOIN wpra_term_taxonomy ON (wpra_term_taxonomy.term_taxonomy_id=wpra_term_relationships.term_taxonomy_id AND wpra_term_taxonomy.taxonomy='category')
INNER JOIN wpra_terms ON (wpra_terms.term_id=wpra_term_taxonomy.term_id )
WHERE object_id='273960'

Just replace 273960 with your POST ID

Answer 4

Thanks, Deepak Sharma, with your post I can do what I was trying to do! I made some fix for it works. I tried liked your comment, but I can't because I don't have enough points!

SELECT * FROM wp_term_relationships INNER JOIN wp_term_taxonomy ON (wp_term_taxonomy.term_taxonomy_id=wp_term_relationships.term_taxonomy_id AND wp_term_taxonomy.taxonomy='product_cat') INNER JOIN wp_terms ON (wp_terms.term_id=wp_term_taxonomy.term_id ) WHERE object_id='61170'