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

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.

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

It sounds like you can just use a standard WP_Query() for this:

$args = array( 
    'post_type' => 'product', 
    'posts_per_page' => -1, 
    'product_cat' => $cat_id, 
);
$result = new WP_Query( $args );

Try this,

global $wpdb;
$products = $wpdb->get_results("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 = 60
                                 ROUP BY wp_posts.post_title");

You have to change the WHERE wp_term_taxonomy.term_id = 60 depending on your category number like WHERE wp_term_taxonomy.term_id = $cat_no or something like that.