0

I connected to the db and successfully pulled products data. Now I want to to display products from specific category. The store is using woocommerce. The landing page is in different location so I can't import wp functions.

Here's what I come up with so far:

SELECT ID, `post_date` ,  `post_title` ,  `post_content` ,  `guid` FROM  `wp_posts` as post

INNER JOIN wp_term_relationships rs ON rs.object_id = post.ID 
INNER JOIN wp_terms t ON t.term_id = rs.term_taxonomy_id 


WHERE  `post_type` =  "product"  
AND  `post_status` =  "publish"     
AND rs.term_taxonomy_id  = 117

ORDER BY post_date DESC LIMIT 5

Nothing happens. If I remove AND rs.term_taxonomy_id = 117 it works as it did before. 5 oldest product appears. Not the products from specific category. What am I missing here?

2

I check your query and it's working fine without any errors or empty results. But in addition if you remove INNER JOIN from wp_terms table its also working because you are not getting anything from that table and it is not used in WHERE clause also.

SELECT ID, `post_date` ,  `post_title` ,  `post_content` ,  `guid` FROM  `wp_posts` as post

INNER JOIN wp_term_relationships rs ON rs.object_id = post.ID 

WHERE  `post_type` =  "post" //post type post for my example
AND  `post_status` =  "publish"
AND rs.term_taxonomy_id  = 1 //term_taxanomy_id is 1 for my example

ORDER BY post_date DESC LIMIT 5
  • For post its working fine. But for products its not. Product have texonomy product_cat. When I run this I get "MySQL returned an empty result set (i.e. zero rows)." But I have products in that category. – john254 Jul 23 '14 at 15:15
  • ok will check it. :) – ksr89 Jul 24 '14 at 4:37
  • @john254 run this query in your mysql and check is their any result. – ksr89 Jul 24 '14 at 5:13
-3
SELECT 
        `ID`, 
        `post_title`,
        `guid`      
FROM 
        posts 
WHERE 
        post_type='product' and 
        post_status='publish' and 
        ID IN(
                SELECT 
                        object_id 
                FROM 
                        term_relationships 
                WHERE 
                        term_taxonomy_id IN(YOUR_CATEGORY_ID_OR_MULTIPLE_IDS_COMMA_SEPARATED ) and
                        term_taxonomy_id IN(
                                            SELECT 
                                                term_taxonomy_id 
                                            FROM 
                                                term_taxonomy 
                                            where 
                                                taxonomy='product_cat' )
                                           )
  • 2
    Please clean up your code and please file an edit and explain what your code does and how it works – Pieter Goosen Dec 18 '14 at 17:55

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.