Help speeding up script (PHP&MySQL)

[shawjames]

So I made a quick little script that checks my sales history for the year and gives me a table with per-month breakdown. The problem is it runs very slow, prob because I am a novice coder and do not know a slick way to do it better... I am sure there is a GROUP BY way to make it easier. Here is what I have:

$result[result] is the array of products that feeds into this.

// GET SALES HISTORY BY MONTH
print "<table border=1>";
while($list = mysql_fetch_array($result[result])){
$mon_ary=array("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec");
$total = 0;
foreach ($mon_ary as $mon){
$where = "ds_orders.onum=ds_carts.onum and ds_carts.pid=$list[pid] and ds_orders.cshipdate like '$mon%2004'";
// ds_orders.cshipdate is in format of (Jan. 01, 2005)
$Q = "select count(ds_carts.pid) from ds_carts, ds_orders where $where";
$count = $db->mySQL_Array($Q);
print "<td>$count[0]</td>";
$total += $count[0];
}
print "<td><b>$total</b></td></tr>";
}
print "</table>";

Now I am sure the reason it takes to long is because it hits the db 12 times for each product.... anyone know of a slick way to do a group by and only run once per product?

Cheers!
-Shaw

[bastien]

what is the basic table structre and the data you wish to show?

[kpzani]

// GET SALES HISTORY BY MONTH
print "<table border=1>";
while($list = mysql_fetch_array($result[result])){
$total = 0;

$Q = "select count(ds_carts.pid) from ds_carts, ds_orders where ds_orders.onum=ds_carts.onum and ds_carts.pid=$list[pid] AND ds_orders.cshipdate LIKE '%2004' GROUP BY ds_orders.cshipdate";
// dunno anything about this OO abstracted db access stuff

$res = @($Q);
while ($count = @mysql_fetch_array($res)) {
print "<td>$count[0]</td>";
$total += $count[0];
}
print "<td><b>$total</b></td></tr>";
}
print "</table>";