Hi Guys, I have been asked to wr

[JamieFuller]

Hi Guys,

I have been asked to write a 'wallboard' script that shows where all our engineers are scheduled to be between 2 dates...

the problem is my code is pretty shite,

It works and it works well but it is super slow. and if I try to do more than 14 or so days I might as well go and make tea.

currently I read a table to get a list of all engineers, I then read from 3 seperate tables for each day and each engineer.

there has got to be a better way, any ideas?


<?
echo &quot;<table border=\&quot;0\&quot; width=\&quot;100%\&quot; class=\&quot;bdr\&quot; cellpadding=\&quot;2\&quot; cellspacing=\&quot;0\&quot;><tr bgcolor=\&quot;#aaaaaa\&quot;><td>Engineer</td>&quot;;
for ($dt3=$dt1;$dt3<$dt2;$dt3+=86400)
{
echo &quot;<td align=\&quot;center\&quot;>&quot;.date(&quot;l&quot;,$dt3).&quot;<br>&quot;.date(&quot;d-M&quot;,$dt3).&quot;</td>&quot;;
}
echo &quot;</tr>&quot;;
$query = &quot;SElect * FROM MstEngineer;&quot;;
$query= mssql_query($query);
//echo &quot;<table border=\&quot;0\&quot; width=\&quot;100%\&quot; class=\&quot;bdr\&quot;>&quot;;
$c=0;
while($row = mssql_fetch_array($query)) {
$eid=$row[0];
$name=$row[2].&quot; &quot;.$row[1];
echo &quot;<tr><td class=\&quot;bdr2\&quot; valign=\&quot;top\&quot;>$row[1] $row[2]</td>&quot;;
for ($dt3=$dt1;$dt3<$dt2;$dt3+=86400)
{
$jones=&quot;&quot;;
$dt5=date(&quot;D&quot;,$dt3);
$bg=&quot;&quot;;
if (($dt5==&quot;Sat&quot||($dt5==&quot;Sun&quot) $bg=&quot; bgcolor =\&quot;#dddddd\&quot;&quot;;
$dt4=date(&quot;Y-m-d&quot;,$dt3);
echo &quot;<td class=\&quot;bdr2\&quot;$bg>&quot;;
$query2 = &quot;SElect * FROM VIEW_IS_Calendar_details WHERE EngineerId=$eid and visited='$dt4';&quot;;
$query2= mssql_query($query2);
while($row2 = mssql_fetch_array($query2)) {
$jones.= $row2[0].&quot; &quot;.$row2[1].&quot;<br>&quot;;
}

$query2 = &quot;SElect OffType FROM viewholiday WHERE EngineerID=$eid and ('$dt4' BETWEEN startdate and enddate);&quot;;
$query2= mssql_query($query2);
while($row2 = mssql_fetch_array($query2)) {
$jones.= $row2[0].&quot;<br>&quot;;
}

$query2 = &quot;SElect LocationDesc FROM viewinstallation WHERE EngineerID=$eid and ('$dt4' BETWEEN startdate and enddate);&quot;;
$query2= mssql_query($query2);
while($row2 = mssql_fetch_array($query2)) {
$jones.= $row2[0].&quot;<br>&quot;;
}

if($jones==&quot;&quot$jones=&quot; &quot;;
echo $jones;
echo &quot;</td>&quot;;
}
echo &quot;</tr>&quot;;
}
echo &quot;</table>&quot;;
?>


Any ideas for optimisation would be great!


Kindest Regards
Jamie

 

[Foamcow]

Use table joins and just make 1 query?