1

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?

  • Why do you need to access this with straight up SQL when there is the WP_Query class that can do it for you? – Cedon May 21 '17 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 – bbruman May 21 '17 at 19:04
5

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

  • SQL knowledge is always a big plus+ – Html Tosin May 20 '18 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.

  • 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 – bbruman May 21 '17 at 19:36
  • What are you trying to access this with if not WordPress? – Cedon May 21 '17 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). – bbruman May 21 '17 at 19:43
  • 1
    You could use the REST API potentially then. – Cedon May 21 '17 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.