生成按 user_id 分组的订单表，该表汇总了产品数量 [英] Generate a table of orders grouped by user_id which sums quantity of product

问题描述

我有一个很大的 mysql 表(在 woocommerce 中)，人们可以在其中多次购买物品.我想要做的是动态生成一个表格，在其中选择我要查询的产品(如果可能，从 php 的下拉菜单中)并将 user_id 购买的产品数量分组.我找到了 this 代码，但有无法生成我想要的表格.这是我当前的测试代码，尝试使其工作但未生成表；

I have a large mysql table (in woocommerce) where people can purchase items more than once. What I would like to do is have a table dynamically generate where I select the product I want to query for (from a dropdown menu in php if possible) and group the quantity of the product purchased by user_id. I found this code, but have been unable to generate the table I would like to get. here is my current test code to try and make it work but no table is generated;

<?php 
if (!is_user_logged_in() || !current_user_can('manage_options')) wp_die('This page is private.');

$con=mysqli_connect("ip","user","pass","database");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,
"select p.user_id,
       max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
       max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
       max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
       group_concat(distinct oi.order_item_name separator '|' ) as order_items
from wp_posts p join
    wp_postsmeta pm
    on p.ID = pm.post_id join
    wp_woocommerce_order_items oi
where p.post_type = 'shop_order' and
      p.post_status = 'wc-completed' and
      oi.order_item_name = 'Product Name'
group p.user_id");
echo "<table>";
while($row = mysqli_fetch_array($result))
{
echo "<tr style='font-size: 0.665em'>";
echo "<td>"  . $row['billing_first_name'] . "</td>";
echo "<td>" . $row['_billing_last_name'] . "</td>";
echo "<td>" . $row['billing_address_1'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>

我的最终目标是使用下拉菜单根据产品 ID 选择有问题的产品，这样我就可以看到每个用户购买了多少查询的产品(仅根据上面的代码完成订单)；

my ultimate goal would be this with a dropdown menu to select product in question based on product ID so I can see how much each user has purchased of the product queried (completed orders only as per code above);

user ID | Product | quantity | billing address 
23      | chair   | 4        | 22 Bank street
42      | chair   | 12       | 123 Smith Road
88      | chair   | 5        | 3 Parker avenue

等

推荐答案

我不知道您为什么在 select 中使用子查询.您还选择了不需要/不需要的列.

I'm not sure why you are using a subquery in the select. You are also selecting columns that you don't want/need.

这符合您的要求吗?

select p.user_id,
       max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
       max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
       max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
       group_concat(distinct oi.order_item_name separator '|' ) as order_items
from wp_posts p join
     wp_postmeta pm
     on p.ID = pm.post_id join
     wp_woocommerce_order_items oi
     on p.ID = oi.order_id
where p.post_type = 'shop_order' and
      p.post_status = 'wc-completed' and
      oi.order_item_name = 'Product Name'
group by p.user_id;

我不得不推测一些列的来源，因为你的问题不明确.

I had to speculate about where some columns come from, because your question is not explicit.

这篇关于生成按 user_id 分组的订单表，该表汇总了产品数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！