Loops within Loops

[Fearpig]

Hi Guys,
I'm tring to change my code from a MySQL database to an SQL database. The code below should list the members of each department under the department heading. The code below just lists the name of the first department 12 times (there are 13 departments in total if that helps).

[code]
include("../ConnectSQL2005.php");

$sql="SELECT * FROM tbl_department";
$result=odbc_exec($conn,$sql);
if (!$result)
      {exit("Error in SQL");}
 
$DeptID=odbc_result($result,"Dept");
$Department_Name=odbc_result($result,"Description");

while (odbc_fetch_row($result))
{

echo "<table>\n";
echo "<tr><td><b class='Body2'>$Department_Name</b></td></tr>";

$sub_sql="SELECT * FROM qry_Full_Employee_Details";
$subresult=odbc_exec($conn,$sub_sql);
if (!$subresult)
      {exit("Error in SQL");}

$Department=odbc_result($subresult,"Department");       
$First_Name=odbc_result($subresult,"First_Name");
$Last_Name=odbc_result($subresult,"Last_Name"); 

while (odbc_fetch_row($subresult))
{
                                  if ($DeptID == $Department)
{
echo "<tr><td>$First_Name $Last_Name</td></tr>";
}
                }
echo "</table>\n";
                echo '<br><br>';
}
[/code]

I'd expect this to show as:

[b]Department 1[/b]
Joe Bloggs
Paul Smith
John Doe

[b]Department 2[/b]
Random Guy
Jane Doe

[b]Department 3[/b]
Someone Else
Yet Another

Can anyone suggest where I am going wrong? I have had this working from an MySQL database and this is the final straw stopping me from putting the SQL based site on-line! Any help would be appreciated.  :)

Cheers
Tom

[argoSquirrel]

You are only setting $DeptID 1 time outside your outer loop so it will always be set to Dept 1.  Then you are returning the whole set of employees and checking it in php so it will only display those few.

First, set your $DeptID in the loop so you keep getting a new one every iteration.

Then try changing your query to:

$sub_sql="SELECT First_Name, Last_Name FROM qry_Full_Employee_Details WHERE department = '" . $DeptID . "'";

That will return a much smaller result set and make it run much faster as it only returns the info you need for the people in that department.  You can stop checking for departments then in your inner loop. 

That should clean up the logic and make it run a lot faster with larger data sets.

Cheers,
Argo

[Fearpig]

Thanks for that Argo Squirrel!  ;D
I've gone over the code as you suggested and its up and running now.