Jump to content

Loops within Loops


Fearpig

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.