Sum the total amount of a specific product purchased per user and display in table

I asked this in Stackoverflow but figured this would be a more specific domain to answer my question…

I have a large mysql table (in woocommerce) where people can purchase items more than once.

My goal is to be able to group the quantity of the product purchased by user_id so that each row is user and product specific.

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

I found this code, but have been unable to generate the table I would need. In future, I would like to add the ability to only show specific products in the table (via SKU) and perhaps their status. I would add drop down menus to dynamically alter the sql query so the table generates what I want. But figuring out how to get the table to generate in the first place is my number one priority!

<?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>”;
?>

Read more here:: Sum the total amount of a specific product purchased per user and display in table

Leave a Reply

Your email address will not be published. Required fields are marked *