如何使用mysql -php根据category_id检索woocommerce产品详细信息

Question

I have to retrieve the product details such as image title,description,image link and price from woocommerce table.

I've retrieved the categories with this Query,

$result = mysql_query("SELECT term_id,name,slug FROM wp_terms WHERE term_id 
    IN (SELECT term_id FROM wp_term_taxonomy WHERE parent='0' 
    AND taxonomy='product_cat') ORDER BY name ASC");

Based on this category id I'm retrieving the subcategories like this:

  $result = mysql_query("SELECT term_id,name,slug FROM wp_terms WHERE term_id
     IN (SELECT term_id FROM wp_term_taxonomy WHERE parent='$cat_id'
     AND taxonomy='product_cat') ORDER BY name ASC");

I've tried a query to get the product details,

 $result = mysql_query("SELECT  `ID`,`post_title`,`post_content`,`guid` FROM wp_posts WHERE
post_type='product' and post_status='publish' and ID IN(SELECT object_id
FROM wp_term_relationships WHERE term_taxonomy_id IN('$cat_id') and 
term_taxonomy_id IN(SELECT term_taxonomy_id FROM wp_term_taxonomy 
where taxonomy='product_cat'))");

This is giving me the title and description when I execute it in phpmyadmin but when I give the same code in php and sending the response through json then it is giving me the empty response.

I think my query is not proper.

Kindly suggest me how to get the product details from woocommerce,am working with it for the first time.

Answer 1

At last I've DONE IT

Here goes the procedure of steps:

    --->  get `id` of product from `wp_posts`
    --->  pass it as a `post_id` to `wp_postmeta` and get the `meta_value` where `meta_key`='_thumbnail_id'
    --->  now send this `meta_value` as `id` of the product to `wp_posts `
    --->  in the field `guid` we have the link of image of the product

To get the price of the product:

---> Send `id` as a `post_id` to `wp_postmeta` and get the `meta_value` where `meta_key`='_price'

Product description and product title are stored in the table wp_posts in the fields post_title and post_excerpt respectively.

I've executed all these queries separately. That's why I'm not adding them in this answer.I'll edit this answer once am done with joins

Happy coding.. :)