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>";
?>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s