[SOLVED] Reducing processing time

Fearpig · November 6, 2007

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 · November 6, 2007

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 · November 6, 2007

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 · November 6, 2007

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

echo $row['jan'] etc

Fearpig · November 6, 2007

Hi Barand,

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

Cheers.

Tom

Barand · November 6, 2007

What's the structure of your sales table?

Fearpig · November 6, 2007

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 · November 6, 2007

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

Fearpig · November 6, 2007

Month is just 1,2,3...

Barand · November 6, 2007

is the "account" the customer_id?

Fearpig · November 6, 2007

Yep...

Barand · November 6, 2007

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 · November 6, 2007

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 · November 6, 2007

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

Fearpig · November 6, 2007

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 · November 6, 2007

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 · November 6, 2007

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 · November 6, 2007

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
?>

Sign In

[SOLVED] Reducing processing time

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information