我在Stackoverflow中问过这个问题，但我想这将是一个更具体的域来回答我的问题。。。
我有一个很大的mysql表（在woocommerce中），人们可以在其中多次购买物品。
我的目标是能够按user\\u 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
等
我发现this 代码，但无法生成所需的表。将来，我想添加只在表中显示特定产品（通过SKU）的功能，或许还可以显示它们的状态。我会添加下拉菜单来动态更改sql查询，以便表生成我想要的内容。但是，首先弄清楚如何生成表是我的首要任务！
<?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 = \'_sku\' and p.ID = pm.post_id THEN pm.meta_value END ) as _sku,
max( CASE WHEN pm.meta_key = \'_amount\' and p.ID = pm.post_id THEN pm.meta_value END ) as _amount,
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[\'user_id\'] . "</td>";
echo "<td>" . $row[\'amount\'] . "</td>";
echo "<td>" . $row[\'billing_address_1\'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
最合适的回答,由SO网友:Bjorn 整理而成
WooCommerce：获取所有用户的订单信息
我想帮你走。。过了一会儿，我有了一个完整的解决方案：S。。。
这里是一个经过测试的单一短代码，它可以输出<table> （仅限WooCommerce 3+公司）：
add_shortcode(\'user_order_info_table\', \'sc_user_order_info_table_callback\');
function sc_user_order_info_table_callback() {
$result_array = array();
$users = get_users();
if($users) {
foreach ($users as $user) {
$products_ordered = array();
$order_args = array(
\'posts_per_page\' => -1,
\'meta_key\' => \'_customer_user\',
\'meta_value\' => $user->ID,
\'post_type\' => \'shop_order\',
\'post_status\' => \'wc-completed\', // only get completed orders
);
$orders = get_posts($order_args);
if($orders) {
foreach ($orders as $order) {
$wc_order = wc_get_order( $order->ID );
foreach ($wc_order->get_items() as $product) {
$wc_product = wc_get_product($product->get_product_id());
if($wc_product) { // make sure the product still exists
if( ! isset( $products_ordered[$product->get_product_id()] ) ) {
$products_ordered[$product->get_product_id()] = array(
\'name\' => $product->get_name(),
\'qty\' => $product->get_quantity(),
);
} else {
$products_ordered[$product->get_product_id()][\'qty\'] = $products_ordered[$product->get_product_id()][\'qty\'] + $product->get_quantity();
}
// get sku example
//$wc_product->get_sku();
}
}
}
}
$customer = new WC_Customer( $user->ID );
$billing_data = $customer->get_billing(\'view\');
$user_billing_address1 = $billing_data[\'address_1\'];
// we have collected all data, save it to array
$result_array[$user->ID] = array(
\'products_ordered\' => $products_ordered,
\'address1\' => $user_billing_address1,
);
}
} else {
}
// shortcode html output begins hebrev
$return_html = \'<table><thead><tr><td>User ID</td><td>Product</td><td>Quantity</td><td>billing address 1</td></tr></thead><tbody>\';
foreach ($result_array as $user_id => $data) {
if( isset($data[\'products_ordered\']) && $data[\'products_ordered\'] ) {
foreach ($data[\'products_ordered\'] as $product_id => $product_data) {
$return_html .= \'<tr>\';
$return_html .= \'<td>\'.$user_id.\'</td>\';
$return_html .= \'<td>\'.$product_data[\'name\'].\'</td>\';
$return_html .= \'<td>\'.$product_data[\'qty\'].\'</td>\';
$return_html .= \'<td>\'.$data[\'address1\'].\'</td>\';
$return_html .= \'</tr>\';
}
}
}
$return_html .= \'</tbody></table>\';
return $return_html;
}
将此代码段放入(
child) 主题功能。php。
使用短代码[user_order_info_table] 在任何你想要的地方的帖子和页面上。
工作时查看here 在我的沙箱里。
注意：这是可行的，但这还不是一个“最终版本”可供运输。您可能需要进行更多的错误处理等。
你好，比约恩
