[RESOLVED] Nested Loops and MySQL Queries

[eqdigital]

There seems to be an issue here, because I'm seeing the resulting revenue data duplicated in the db output.

The revenue seems to loop over the reported time periods and settings... so maybe this is the part that's not working.

Resulting rows in db look something like this (I've just picked one city for an example, many are returned in the query for each product):

City | Date ID | Product ID | Sales

Dublin 78 145088 246743
Dublin 127 145088 243198
Dublin 78 145088 246743
Dublin 127 145088 243198

However, based on the underlying sales data, the following should be the output:

City | Date ID | Product ID | Sales

Dublin 32 145088 157418
Dublin 78 145088 246743
Dublin 127 145088 243198

I'm fairly sure at this point that I've implemented these loops incorrectly - I have a couple ideas of how to approach this differently, but am looking for some outside opinions on how to resolve...

Objective - Create Monthly Sales Reports by Product ID and City.

DB Table:
Table contains sales reports by Product ID, City name, Date ID.

Note: Months are identified here by an ID#, not date/time. (ex. January 1999 = 1, April 2010 = 136)

My first query checks the db table to see product IDs have compiled reports and uses the result to create a while loop on Product IDs.

Now that I have a Product ID...

Nested inside the first loop is a second while loop created on City names result of query of the table for distinct City names where there is sales data for the product id.

Now I have a Product ID and a set of Cities to create reports.

Nested inside the second loop is another while loop on Date IDs result of query of db for distinct date IDs where report data is present in the db table.

So the objective is to create sales reports for Product IDs by City and Date ID.

Get the product id, find the cities, find the date IDs where there is data and loop through to create reports.

Inside this last loop I have the query that uses the ProductID, DateID, and City Name to query the db and insert the calculated values. I've cut down the looped db insert here for posting purposes.

Any help and guidance will be much appreciated. Thanks in advance.

$db = new mysqldb();
$res = $db->query("SELECT DISTINCT PRODUCT_ID from SALES_REPORTS");
$row = $db->fetchQueryArray($res);

while($row = $db->fetchQueryArray($res)){
$productid = $row['PRODUCT_ID'];


$citidates = $db->query("SELECT DISTINCT CITY,STATE,COUNTRY FROM SALES_REPORTS WHERE PRODUCT_ID = '".$productid."'");
$ctrow = $db->fetchQueryArray($citidates);

while ($ctrow = $db->fetchQueryArray($citidates)){
$qcity = addslashes($ctrow['CITY']);
$qstate = addslashes($ctrow['STATE']);
$qcountry = $ctrow['COUNTRY'];



$procdates = $db->query("SELECT DISTINCT DATE_ID FROM SALES_REPORTS WHERE PRODUCT_ID = '".$productid."' AND CITY = '".$qcity."'");
$pdrow = $db->fetchQueryArray($procdates);

	while ($pdrow = $db->fetchQueryArray($procdates)){
	$dateid = $pdrow['DATE_ID'];



$cres = $db->query("SELECT SUM(SALE_PRICE) as tot_sale
					FROM SALES_REPORTS WHERE PRODUCT_ID = '".$productid."' 
					AND DATE_ID = '".$dateid."' 
					AND CITY = '".$qcity."'");

$crow = $db->fetchQueryArray($cres);

$avg_sale = $crow['tot_sale'];


$values = array(PRODUCT_ID => $productid,CITY => $qcity,DATE_ID => $dateid,STATE => $qstate,COUNTRY => $qcountry,TOT_SALE => $tot_sale);


					$db->insert('table', $values);

}

}

}

[laserlight]

You should be able to simplify to a single query, maybe something like:

SELECT CITY, DATE_ID, PRODUCT_ID, SUM(SALE_PRICE) AS tot_sale
    FROM SALES_REPORTS
    GROUP BY PRODUCT_ID, DATE_ID, CITY

[eqdigital]

Indeed a better way to approach the problem. Thank you very much.