EEk, Not sure how to code this.

Noodleman

Hi, im newish to processing data with PHP, and im stuck on something at the moment.

Here is what im trying to achieve:

1) I need to get a unique list of customers from the mysql database for the chosen month/year

2) For Each customer that is found I need to add up the total of "COLUMN1" "COLUMN2" and "COLUMN3" for the chosen month/year

3) Display the results in a table, listing the total for each customer.

It sounds simple when i look at what I need to do, but im stuck 🙁
Here is some example data:

ID	COLUMN1	COLUMN2	COLUMN3	MONTH	YEAR  CUST
1	3	2	1	March	2003 TEST
2	4	2	4	January	2004 TEST1
3	4	2	1	January	2004 TEST2
4	5	3	5	June	2004 TEST3
5	2	4	3	February  2004 TEST4

So, for example, The user has selected all records from January 2004. The results in the Table should be:

Customer Total
TEST1 10
TEST2 7

So, can anyone offer any pointers on the best method to do this?

Many thanks in advance

Shrike

$total = 0;
$cust = "TEST1";
$month = "January 2004";
$sql = "SELECT cust, COUNT(column1, column2, column3) AS total FROM table WHERE month = '$month' AND cust = '$cust'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $total += $row['total'];
}
echo "$cust total for $month is $total";

I guess you could retrieve all customers at once but then you'd have to parse the rows for the customer name.

Code is untested sorry 🙂

Ah to obtain a unique list of customers (this would allow you to do another loop around the one above, iterating through the customers list):

$customers = array();
$sql = "SELECT DISTINCT cust FROM table";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $customers[] = $row['cust'];
}
foreach($customers as $customer){
  etc ...
}
edit: oops DISTINCT not UNIQUE :)

Noodleman

That would only work for one customer though, not multiple customers?