6

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?

Improve this question
2
  • Why do you need to access this with straight up SQL when there is the WP_Query class that can do it for you? Commented May 21, 2017 at 18:44
  • Never heard of that. I'll look into it. I'm just messing around with mass editing my post_content in the wp_posts table on my localhost in my own PHP script outside of WordPress. Then importing it back to my live site kind of thing. Worked just fine till I needed to filter categories Commented May 21, 2017 at 19:04

4 Answers 4

Reset to default
15

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

Improve this answer
1
  • 1
    SQL knowledge is always a big plus+ Commented May 20, 2018 at 9:49
1

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.

Improve this answer
4
  • Thank you! I'll accept this answer if I can't find a solution that doesn't depend on depend on the WP framework. Want to look a little more into this you would think there would be some way to join the tables together through a manual query... but maybe not Commented May 21, 2017 at 19:36
  • What are you trying to access this with if not WordPress? Commented May 21, 2017 at 19:38
  • I'm just exporting the tables I need from WordPress, doing some heavy testing and experimenting with them on localhost. Mostly to try and bulk edit the post_content column and bring it back to live. Since post_content is just simply blocks of html code, the framework wasn't required (till now, maybe). Commented May 21, 2017 at 19:43
  • 1
    You could use the REST API potentially then. Commented May 21, 2017 at 20:21
1

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

Improve this answer
1
  • This is not the answer to the question. It was about getting posts from a specific category (the title is a bit misleading). Commented Jun 2, 2019 at 11:12
0

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'
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.