[SOLVED] Reducing processing time

[Fearpig]

Hi Guys,

I've written the page below which creates a table of sales figures from an sql database, unfortunately the way I have written it if someone has 40 customers the database hase to run about 520 select queries. Can anyone see a more streamlined way of doing this?

 

What I am basically trying to get is a table with these columns:

 

Customer ID, Jan, Feb, Mar, April, May ..... Dec, Year-To-Date

 

Under the YTD and month columns there is just a quantity and total value. Anway here's the code if anyone can help I'd appreciate it as every time someone has a look at their sales page the whole webserver locks up!

 

<?php

$sql="SELECT * FROM qry_Sales_Accounts_by_Customer WHERE Year = '$Year' AND Area = '$id'";
$result=odbc_exec($conn,$sql);

if (!$result)
       {exit("Error in SQL");}

while (odbc_fetch_row($result)) 
	{
	$Account=odbc_result($result,"Account");

	echo "<tr align='right' bgcolor='#FFE0CC' class='Body2'><td align='center'>$Account</td>";


//----------Start of 12 month loop---------		
	$Month_by_Cust = 0; 

	while($Month_by_Cust < 12){

		echo "<td>";
		$Month_by_Cust = $Month_by_Cust + 1;
		//echo "$Month_by_Cust<br>$Year<br>$id<br>$Account";



$sql_month="SELECT * FROM qry_Sales_by_Customer WHERE Year = '$Year' AND Month = '$Month_by_Cust' AND Area = '$id' AND Account = '$Account'";
$result_month=odbc_exec($conn,$sql_month);

if (!$result_month)
{exit("Error in SQL");}

while (odbc_fetch_row($result_month)) 
	{
	$Price=odbc_result($result_month,"SumPrice");
	$Price = number_format($Price);
	$Quantity=odbc_result($result_month,"SumQuantity");
	$Quantity = number_format($Quantity);

	echo "$Quantity<br>£$Price";

	}
	echo "</td>";
	}


	//-----------------Year To Date Totals----------------------------

	echo "<td>";

	$sql_year="SELECT * FROM qry_Sales_by_Customer_by_Year WHERE Year = '$Year' AND Area = '$id' AND Account = '$Account'";
	$result_year=odbc_exec($conn,$sql_year);

	if (!$result_year)
	     {exit("Error in SQL");}

	while (odbc_fetch_row($result_year)) 
			{
			$Price=odbc_result($result_year,"SumPrice");
			$Price = number_format($Price);
			$Quantity=odbc_result($result_year,"SumQuantity");
			$Quantity = number_format($Quantity);


			echo "$Quantity<br>";
			echo "£$Price";
			}

	echo "</td>";
	echo "</tr>";


	}

?>

[Barand]

try

<?php
$sql_month="SELECT customer_id,
                SUM(IF(month=1, price, 0)) as jan,
                SUM(IF(month=2, price, 0)) as feb,
                SUM(IF(month=3, price, 0)) as mar,
                . . .
                SUM(IF(month=12, price, 0)) as dec,
                SUM(quantity) as ytdqty,
                SUM(price) as ytdprice
            FROM qry_Sales_by_Customer 
            WHERE Year = '$Year' 
                AND Area = '$id' 
                AND Account = '$Account'
            GROUP BY customer_id";
?>

[Fearpig]

Hi Barand,

That looks spot on to me! Hopefully if I do it like that it'll only be 1 query per customer.

 

1 question though...

Once I've got that query into my page how do I print the values? Is it just a case of echo "jan"; ?

 

[Barand]

1 query by year, id, account list totals for all customers

 

 

echo $row['jan'] etc

[Fearpig]

Hi Barand,

Have you got an example or tutorial like this that works as I'm having trouble adapting this one.

 

Cheers.

Tom

 

[Barand]

What's the structure of your sales table?

[Fearpig]

Table: qry_Sales_by_Customer

 

Fields:

 

Account

Price

Quantity

Month

Year

Area

 

But if a customer hasn't bought anything in a month then there is no row for that month, there are no rows with 0 quantity.

[Barand]

is month 1,2,3 or jan, feb, mar ?

[Fearpig]

Month is just 1,2,3...

[Barand]

is the "account" the customer_id?

[Fearpig]

Yep...

[Barand]

pass the year and id (area) to this

<?php
$sql_month="SELECT Account,
                SUM(IF(month=1, price, 0)) as jan,
                SUM(IF(month=2, price, 0)) as feb,
                SUM(IF(month=3, price, 0)) as mar,
                SUM(IF(month=4, price, 0)) as apr,
                SUM(IF(month=5, price, 0)) as may,
                SUM(IF(month=6, price, 0)) as jun,
                SUM(IF(month=7, price, 0)) as jul,
                SUM(IF(month=8, price, 0)) as aug,
                SUM(IF(month=9, price, 0)) as sep,
                SUM(IF(month=10, price, 0)) as oct,
                SUM(IF(month=11, price, 0)) as nov,
                SUM(IF(month=12, price, 0)) as dec,
                SUM(quantity) as ytdqty,
                SUM(price) as ytdprice
            FROM qry_Sales_by_Customer 
            WHERE Year = '$Year' 
                AND Area = '$id' 
            GROUP BY Account";
?>

[Fearpig]

Cheers for that Barand,

But I'm still in the dark as to how to print the array now the values are in there. Can you recommend a tutorial on arrays as I think I need to go back to basics with them!

[Barand]

http://www.w3schools.com/php/php_arrays.asp

[Fearpig]

Hi Barand...

Sorry you seem to be holding my hand through this one!

Here's the code I have so far, its also got the quantities for each month and I've hard coded the variables until I get it working:

 

<?php

$sql_month="SELECT Account,
                SUM(IF(month=1, price, 0)) as price_jan,
                SUM(IF(month=2, price, 0)) as price_feb,
                SUM(IF(month=3, price, 0)) as price_mar,
                SUM(IF(month=4, price, 0)) as price_apr,
                SUM(IF(month=5, price, 0)) as price_may,
                SUM(IF(month=6, price, 0)) as price_jun,
                SUM(IF(month=7, price, 0)) as price_jul,
                SUM(IF(month=8, price, 0)) as price_aug,
                SUM(IF(month=9, price, 0)) as price_sep,
                SUM(IF(month=10, price, 0)) as price_oct,
                SUM(IF(month=11, price, 0)) as price_nov,
                SUM(IF(month=12, price, 0)) as price_dec,                
   SUM(IF(month=1, quantity, 0)) as quantity_jan,
                SUM(IF(month=2, quantity, 0)) as quantity_feb,
                SUM(IF(month=3, quantity, 0)) as quantity_mar,
                SUM(IF(month=4, quantity, 0)) as quantity_apr,
                SUM(IF(month=5, quantity, 0)) as quantity_may,
                SUM(IF(month=6, quantity, 0)) as quantity_jun,
                SUM(IF(month=7, quantity, 0)) as quantity_jul,
                SUM(IF(month=8, quantity, 0)) as quantity_aug,
                SUM(IF(month=9, quantity, 0)) as quantity_sep,
                SUM(IF(month=10, quantity, 0)) as quantity_oct,
                SUM(IF(month=11, quantity, 0)) as quantity_nov,
                SUM(IF(month=12, quantity, 0)) as quantity_dec,
                SUM(quantity) as ytdqty,
                SUM(price) as ytdprice
            FROM qry_Sales_by_Customer 
            WHERE Year = '2007' 
                AND Area = '0032' 
            GROUP BY Account";


$result=odbc_exec($conn,$sql);

if (!$result)
       {exit("Error in SQL");}

while (odbc_fetch_row($result)) 
	{

	echo $row['price_jan'];
	}

?>[code]

Unfortunately I'm just getting "Undefined variable: row" as I haven't declared what $row is! 
Where do I set up the array or do I just need to change the way I’m trying to output the results?

[/code]

[Barand]

try

<?php
$sql_month="SELECT Account,
                SUM(IF(month=1, price, 0)) as jan,
                SUM(IF(month=2, price, 0)) as feb,
                SUM(IF(month=3, price, 0)) as mar,
                SUM(IF(month=4, price, 0)) as apr,
                SUM(IF(month=5, price, 0)) as may,
                SUM(IF(month=6, price, 0)) as jun,
                SUM(IF(month=7, price, 0)) as jul,
                SUM(IF(month=8, price, 0)) as aug,
                SUM(IF(month=9, price, 0)) as sep,
                SUM(IF(month=10, price, 0)) as oct,
                SUM(IF(month=11, price, 0)) as nov,
                SUM(IF(month=12, price, 0)) as dec,
                SUM(quantity) as ytdqty,
                SUM(price) as ytdprice
            FROM qry_Sales_by_Customer 
            WHERE Year = '$Year' 
                AND Area = '$id' 
            GROUP BY Account";
$result = odbc_exec($connection, $sql);
while (odbc_fetch_into($result, $data)) {
    vprintf ('<tr><td>%s</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f</td><td>%0.2f</td></tr>', $data);
}
?>

[Barand]

oops

 

<?php
$sql_month="SELECT Account,
                SUM(IF(month=1, price, 0)) as jan,
                SUM(IF(month=2, price, 0)) as feb,
                SUM(IF(month=3, price, 0)) as mar,
                SUM(IF(month=4, price, 0)) as apr,
                SUM(IF(month=5, price, 0)) as may,
                SUM(IF(month=6, price, 0)) as jun,
                SUM(IF(month=7, price, 0)) as jul,
                SUM(IF(month=8, price, 0)) as aug,
                SUM(IF(month=9, price, 0)) as sep,
                SUM(IF(month=10, price, 0)) as oct,
                SUM(IF(month=11, price, 0)) as nov,
                SUM(IF(month=12, price, 0)) as dec,
                SUM(quantity) as ytdqty,
                SUM(price) as ytdprice
            FROM qry_Sales_by_Customer 
            WHERE Year = '$Year' 
                AND Area = '$id' 
            GROUP BY Account";
$result = odbc_exec($connection, $sql);
echo '<table>';                                                                // forgot this
while (odbc_fetch_into($result, $data)) {
    vprintf ('<tr><td>%s</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f</td><td>%0.2f</td></tr>', $data);
}
echo '</table>';                                                               // and this
?>

[Fearpig]

 

Thanks Barand its working now! Had to do a bit of jiggery pokery once I realised I couldn't use IF statements on an MS SQL server but here's where I got to (and it works woo hoo!). Again many thanks for your help on this one. 

 

<?php
$sql_month="SELECT Account,
                SUM(CASE month WHEN '1' THEN SumPrice ELSE 0 END) AS jan,
                SUM(CASE month WHEN '2' THEN SumPrice ELSE 0 END) AS feb,
                SUM(CASE month WHEN '3' THEN SumPrice ELSE 0 END) AS mar,
                SUM(CASE month WHEN '4' THEN SumPrice ELSE 0 END) AS apr,
                SUM(CASE month WHEN '5' THEN SumPrice ELSE 0 END) AS may,
                SUM(CASE month WHEN '6' THEN SumPrice ELSE 0 END) AS jun,
                SUM(CASE month WHEN '7' THEN SumPrice ELSE 0 END) AS jul,
                SUM(CASE month WHEN '8' THEN SumPrice ELSE 0 END) AS aug,
                SUM(CASE month WHEN '9' THEN SumPrice ELSE 0 END) AS sep,
                SUM(CASE month WHEN '10' THEN SumPrice ELSE 0 END) AS oct,
                SUM(CASE month WHEN '11' THEN SumPrice ELSE 0 END) AS nov,
                SUM(CASE month WHEN '12' THEN SumPrice ELSE 0 END) AS dec,
			SUM(SumQuantity) as ytdqty,
                SUM(SumPrice) as ytdprice
		FROM qry_Sales_by_Customer 
            WHERE Year = '2007' 
                AND Area = '0032' 
            GROUP BY Account";

$result = odbc_exec($conn, $sql_month);
echo "<Table border='0' cellpadding='4'><tr class='TitleText_White' bgcolor=#FF6600><th>Area: $id</th><th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th><th>June</th><th>July</th><th>Aug</th><th>Sept</th><th>Oct</th><th>Nov</th><th>Dec</th><th>Y.T.D.</th></tr>";
while (odbc_fetch_into($result, $data)) {
    vprintf ('<tr><td>%s</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td>
                <td>%0.2f<br>%0.2f</td></tr>', $data);
}
echo '</table>';                                                               // and this
?>