slow looping query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
wazsxr
Forum Newbie
Posts: 2
Joined: Fri Aug 31, 2007 1:18 am

slow looping query

Post by wazsxr »

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.

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>
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Few things

1. Use <?php instead of <?

2. You need to filter for bad data. Right now your database is wide open to sql injection because someone can put whatever they want in for $_GET['month']

3. If you ever do a query, loop on the results and do a query for every row, then its wrong, you should be able to solve the problem with a join.

4. You do not need to create a new connection before every query. Ideally you should create one connection and reuse it throughout all of your code. In this case you are creating a new connection, then doing a query, for every single day of the month. Creating connections is slow, so that is why your script is taking so darn long. At the beginning of your script put $conn = mysql_connect(...) then just reuse $conn. That should speed it up a lot. After that work on getting this into one single query.
wazsxr
Forum Newbie
Posts: 2
Joined: Fri Aug 31, 2007 1:18 am

Post by wazsxr »

Cheers for that Begby,
i have made the changes as suggested but I the main thing I was after was help with the query you mention in your last point.
I know it's taking for ever to do the loops because it's running a query on each day of the week but my problem is that I cannot think of any other way of getting the desired output. I've tried google-ing and cannot turn up any help with it, so if you could steer me in the right direction it would be greatly appreciated.
Thanks again.
Post Reply