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?

Read more here: MySQL Query to Retrieve Category from wp_posts


Solution:

If you know the solution of this issue, please leave us a reply in Comment section, to update the question.


Wordpress related questions and answers: