phpmysqldatabasems-query

show one custommer only

i want to make a total sum of the ordered product for on custommer how do i do that?

i have the following code

$result = mysql_query ("SELECT order_item_name, SUM(product_quantity) FROM wntl_virtuemart_order_items WHERE wntl_virtuemart_order_items.order_item_sku BETWEEN 2 and 2.9 AND order_status= 'U'  GROUP BY order_item_sku");   

while($row=mysql_fetch_array($result))   {   
echo "<tr>";   
echo "<td align='center' width='200'>" . $row['order_item_name'] . "</td>";    
echo "<td align='center' width='200'>" . $row['SUM(product_quantity)'] . "</td>";   
echo "</tr>";   
}

with this code i get a overall total i want to see a total for one custommer

thx in advance grtz

Solution

  • If you want counts by customer then you need to specify the customer id in WHERE condition.

    E.g. if customer id is present in wntl_virtuemart_order_items table then your query will look like this:

    SELECT order_item_name, SUM(product_quantity) 
FROM wntl_virtuemart_order_items 
WHERE wntl_virtuemart_order_items.order_item_sku BETWEEN 2 and 2.9 AND order_status= 'U' AND customer_id = ?
GROUP BY order_item_sku

    If customer id is present in another table then you will need to use JOIN two tables like the below example:

    SELECT order_item_name, SUM(product_quantity) 
FROM wntl_virtuemart_order_items JOIN wntl_virtuemart_orders ON wntl_virtuemart_order_items.virtuemart_order_id  = wntl_virtuemart_orders.virtuemart_order_id 
WHERE wntl_virtuemart_order_items.order_item_sku BETWEEN 2 and 2.9 AND wntl_virtuemart_order_items.order_status= 'U' AND wntl_virtuemart_orders.customer_number = ?
GROUP BY order_item_sku