Reduce processing time with Groups

[Fearpig]

Hi Guys,

Can someone have a look at the code below for me? It all works as it is but just takes too long to run up. Can anyone see a way to simplify the process?

 

The page displays a table of people and their jobs for the next 7 days, a bit like this...

 

Paul:

Today:

Job001

Job002

Wednesday:

Job003

Thursday:

Job004

Job005

Job006

Stephen:

Today:

Job007

Job008

Wednesday:

Thursday:

Job009

Job010

Job011

Job012

James:

Today:

Job0013

Wednesday:

Job0014

Thursday:

Job015

Job016

 

Unfortunately at the moment the page runs a SELECT query for every cell of the table (the example above would use 9 queries). This isn't too bad untill I now have 26 people and I'm showing up to 7 days meaning that the one page for this table runs 182 SELECT queries!!

 

Can anyone see a simpler method? My table is set out as:

 

Table:

Job Number

Person

Date

Status

 

...and here is the code I am currently using:

 

<?php
$conn=odbc_connect('OU_PROD','ou_dba','dba');
if (!$conn)
      {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM person WHERE person_group = 'ENGINEER' AND employee = 'Y'";
$rs=odbc_exec($conn,$sql);
if (!$rs)
      {exit("Error in SQL");}

echo "<Table cellspacing='0' cellpadding='0' border='1'><tr valign='top'>";

while (odbc_fetch_row($rs))
{
     $User_ID=odbc_result($rs,"user_id");
     echo "<td class='Body3'>$User_ID";
//The above query sets up a table with one row and one person per cell.
//The query below puts in a table of that persons jobs into the cell created above


$day=0;
//sets the day value back to 0 - days run 0,1,2,3,4,5,6,

$startdate=date("Y/m/d");
//sets the initial date to the current date

echo "<Table border=1 width=110>";

while ($day < 7)
{
      
      $selectdate = date( "Ymd", mktime(0, 0, 0, date("m"), date("d")+$day, date("y")) );
      //increase $startdate by the current value of $day

      $formatdate = date("D - d/m/Y",strtotime("$selectdate")); 

      $sql="SELECT * FROM FER_Engineers_Jobs_ALL WHERE Date = '$selectdate' and person_id = '$User_ID'";
      $result=odbc_exec($conn,$sql);
      if (!$result)
            {exit("Error in SQL");}

      echo "<tr bgcolor=#CCCCCC align=center class='Body3'><th><b>$formatdate</b></th></tr><tr><td>";
  //echo "$day<br>";

     while (odbc_fetch_row($result))
     {
          $Request=odbc_result($result,"request_id");
	  $PostCode=odbc_result($result,"Postcode");
	  $Status=odbc_result($result,"Status");
	  
	  if($Status == 'OPEN                '){$Font = 'Body3Red';}
	  else{$Font = 'Body3';}
	  
          echo "<a href=Result_ByRequest.php?Request=$Request class='$Font'>$Request - $PostCode</a><br>";
     }



$day++;
//Add one to the $day variable before repeating loop

}

echo "</td></tr></Table>";





 echo "<td>";
}

echo "</tr></table>";
odbc_close($conn);
?>

[aschk]

Comile your queries into one :

 

SELECT * FROM person WHERE person_group = 'ENGINEER' AND employee = 'Y'

SELECT * FROM FER_Engineers_Jobs_ALL WHERE Date = '$selectdate' and person_id = '$User_ID'

 

Becomes

 

SELECT j.* 
FROM FER_Engineers_Jobs_ALL j
WHERE person_id IN (
SELECT person_id FROM person p
WHERE person_group = 'ENGINEER' AND employee = 'Y'
)
AND Date = '$selectdate'

[aschk]

It's sort of occured to me that you're not going to get the names of the people in question this way, nor the days of the week.

You'll have to give me a mo.