我有一个大的 mysql 表(在 woocommerce 中),人们可以在其中多次购买商品。我想做的是动态生成一个表,在其中选择我想要查询的产品(如果可能的话,从 php 中的下拉菜单中)并按 user_id 购买的产品数量进行分组。我发现this http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-including-metadata/代码,但无法生成我想要的表。这是我当前的测试代码,尝试使其工作,但没有生成表;
<?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 选择有问题的产品,这样我就可以看到每个用户购买了多少所查询的产品(仅根据上面的代码完成订单);
user ID | Product | quantity | billing address
23 | chair | 4 | 22 Bank street
42 | chair | 12 | 123 Smith Road
88 | chair | 5 | 3 Parker avenue
etc
我不确定你为什么在中使用子查询select
。您还选择了您不想要/不需要的列。
这是你想要的吗?
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;
我不得不猜测一些专栏的来源,因为你的问题不明确。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)