CODEWITHSUNDEEP
phpmysqlwordpresswoocommerce
j jones
j jones

Reputation: 487

mysql - woocomerce how to fetch products in a category

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 .

Upvotes: 1

Views: 3230

Answers (1)

dineshkashera
dineshkashera

Reputation: 1502

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.

Upvotes: 6

Related Questions