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>