1

I'm writing a web service , I need to fetch all products in a category , I've written this 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 =909

the code return 3 items but the problem is , i've about 20 products in this category in my website and in the website it shows all these 20 products .

what is wrong with this code? how can I get all products in a category ? by the way , I'm using woocomrce api either , if I can use it .

j jones
  • 477
  • 7
  • 24

1 Answers1

6

You can get all product related to specific category by using below custom query 

 //your custom category id   
 $cat_ids          =   123;
 $conn          =   new mysqli($servername, $username, $password,$database);

 $get_pro_by_cat   =   'SELECT ID, `post_title` FROM  `wp_posts` as post 
                       INNER JOIN wp_term_relationships rs ON rs.object_id = 
                       post.ID WHERE  `post_type` =  "product" AND `post_status` 
                       =  "publish" AND rs.term_taxonomy_id  =' .$cat_ids.' 
                       ORDER BY post_title';

$avail_products = $conn->query($get_pro_by_cat);

NOTE: Replace your table prefix with wp_ table prefix in above query wp_posts.

You can see the demo at this url : http://cadjewelrygallery.com/ . I hope it will help you.

dineshkashera
  • 1,442
  • 1
  • 15
  • 26
  • thanks for reply ,I've tested this code, it works fine for some categories but it doesn't work for most of them , is there any other way to do so ? – j jones Nov 15 '17 at 13:41
  • If you are using woocommerce rest api then please see this link https://github.com/woocommerce/woocommerce-rest-api-docs/blob/master/source/includes/v3/_product-categories.md . – dineshkashera Nov 15 '17 at 13:46