duanhui4160 2015-01-28 11:40
浏览 43
已采纳

列出Woocommerce产品及其类别ID - MySQL / PHP

I am using a MySQL query to list all product addon variations(swatches), I am able to do this fine, as well as list the product the variation is associated with. The problem I have is listing that products category id as well.

I have no formal training in MySQL so any help and advise on performance etc. will also be of great value!

Here is the code I am using to get the list of addon variations and their products, just need to get the category ID's for those products as well.

$prod_attributes_sql = 'SELECT DISTINCT wt.slug AS term_slug, wtr.term_taxonomy_id, wpp.post_title AS wpost_title, wt.term_id AS wpt_terms, cpt.term_id AS cpt_terms
    FROM '
. " {$wc_price_table->cat_price_table_name} cpt"
. ' RIGHT OUTER JOIN wp_terms wt ON cpt.term_id=wt.term_id'
. ' INNER JOIN wp_term_relationships wtr ON wt.term_id = wtr.term_taxonomy_id'
. ' INNER JOIN wp_posts wpp ON wtr.object_id = wpp.ID'
. ' RIGHT OUTER JOIN wp_term_taxonomy ttx on wt.term_id = ttx.term_id'
. ' WHERE ttx.taxonomy IN (\'' . implode("','", $slugsnews) . '\')'
. ' ORDER BY wt.name';
$prod_attributes = $wpdb->get_results($prod_attributes_sql) or die(mysql_error());

Which outputs the list of addon variations and the product they are associated to. But how would I go about including the category id of those products.

Let me know if I need to add any further explanation or code.

Thanks in advance!

  • 写回答

1条回答 默认 最新

  • dongzhang7157 2015-04-02 10:14
    关注

    I ended up having to change the query to include the $term_id, using the following foreach to get the terms:

            $term_id = array();
            foreach ($product_categories as $key => $category) {
                foreach ($category['terms'] as $term) {
                    $term_id[] = $term->term_id;
                }
            }
    

    and and included in my sql like this:

            $prod_attributes_sql = 'SELECT DISTINCT wt.slug AS term_slug, wt.name, ttx.taxonomy, wtr.term_taxonomy_id, wpp.post_title AS wpost_title, wt.term_id AS wpt_terms, cpt.term_id AS cpt_terms
                FROM '
            . " {$wc_price_table->cat_price_table_name} cpt"
            . ' RIGHT OUTER JOIN wp_terms wt ON cpt.term_id=wt.term_id'
            . ' INNER JOIN wp_term_relationships wtr ON wt.term_id = wtr.term_taxonomy_id'
            . ' INNER JOIN wp_posts wpp ON wtr.object_id = wpp.ID'
            . ' RIGHT OUTER JOIN wp_term_taxonomy ttx on wt.term_id = ttx.term_id'
            . ' WHERE cpt.term_id IN(' . implode(',', $term_id) . ')'
            . ' OR ttx.taxonomy IN (\'' . implode("','", $slugsnews) . '\')'
            . ' OR ttx.term_taxonomy_id = cpt.term_id'
            . ' ORDER BY wpp.post_title';
            $prod_attributes = $wpdb->get_results($prod_attributes_sql) or die(mysql_error());
    

    This works.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 怎么在stm32门禁成品上增加记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 解riccati方程组