slow looping query
Posted: Fri Aug 31, 2007 1:31 am
Hi,
I'm not sure if I'm posting in the right place (sorry).
Basically my problem being I'm looping a query to find out how many records are on each day of the month. This has to be displayed in a table with a list of days of the month for any given month ($month) along the top and "centre name" along the side. The following code does this BUT it's way too slow (because of the number of loops) Is there a way to optimise this in any way? Is there any more info that you need to see to help? Please help. Thank you.
I know this doesn't follow any best practices but I'm self taught...I'm just after some optimisation. cheers.
I'm not sure if I'm posting in the right place (sorry).
Basically my problem being I'm looping a query to find out how many records are on each day of the month. This has to be displayed in a table with a list of days of the month for any given month ($month) along the top and "centre name" along the side. The following code does this BUT it's way too slow (because of the number of loops) Is there a way to optimise this in any way? Is there any more info that you need to see to help? Please help. Thank you.
I know this doesn't follow any best practices but I'm self taught...I'm just after some optimisation. cheers.
Code: Select all
<?
error_reporting(E_ALL);
$month=$_GET['month'];
$year = date('Y');
?>
<table width="200" border="0">
<tr>
<td colspan="3"><? echo date('F Y',mktime(0, 0, 0, $month, 1, $year)); ?></td>
</tr>
<?
function showMonth($month, $year)
{
$date = mktime(12, 0, 0, $month, 1, $year);
$daysInMonth = date("t", $date);
// calculate the position of the first day in the calendar (sunday = 1st column, etc)
$offset = date("w", $date);
$rows = 1;
echo "<table border=\"1\">\n";
?><tr><th>Centre</th><?
for($day = 1; $day <= $daysInMonth; $day++)
{
?><th><? echo date('D',mktime(0,0,0,$month,$day,$year)); ?>
<? }
?></tr>
<? //----------------------Days of week------------ ?>
<tr><td></td><?
for($day = 1; $day <= $daysInMonth; $day++)
{
?>
<td><? echo $day; ?></td>
<?
}
echo '<tr>';
$conn = mysql_connect('server','username',"password");
//mysql_select_db("callcentres",$conn);
$query = "SELECT DISTINCT `Call Centre Name` FROM callcentres.list li LEFT JOIN amd.online o ON o.`agentname`= li.`Call Centre Name` WHERE li.`Current Centre`='Yes' AND li.`Dialling For`='AMD' AND o.`realdate` BETWEEN '$year-$month-1 00:00:00' AND '$year-$month-31 00:00:00' ORDER BY li.`Dialling For`";
$result = mysql_query($query, $conn);
if(mysql_num_rows($result) OR die(mysql_error())) {
while($callcentre = mysql_fetch_object($result))
{
echo '<th>';
$ccn = 'Call Centre Name';
$centrename = $callcentre->$ccn;
echo $centrename; ?></th>
<? for($day = 1; $day <= $daysInMonth; $day++)
{
$conn = mysql_connect('server','username',"password");
mysql_select_db("amd",$conn);
$query = "SELECT * FROM online WHERE `agentname` = '$centrename' AND `realdate`='$year-$month-$day 00:00:00'";
$resultamd = mysql_query($query);
$amdsales = mysql_num_rows($resultamd)
?>
<td><? echo $amdsales; ?></td>
<? }
echo '</tr>';
}
}?>
</tr>
</table>
<?
}
showMonth($month, $year)?>
</table>