Jump to content


Photo

Loops within Loops


  • Please log in to reply
2 replies to this topic

#1 Fearpig

Fearpig
  • Members
  • PipPipPip
  • Advanced Member
  • 195 posts

Posted 10 October 2006 - 10:38 AM

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).

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>';
		}

I'd expect this to show as:

Department 1
Joe Bloggs
Paul Smith
John Doe

Department 2
Random Guy
Jane Doe

Department 3
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
"Whats wrong with the cat?" - Mrs Schrödinger

#2 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 16 October 2006 - 10:05 PM

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

#3 Fearpig

Fearpig
  • Members
  • PipPipPip
  • Advanced Member
  • 195 posts

Posted 18 October 2006 - 08:19 AM

Thanks for that Argo Squirrel!  ;D
I've gone over the code as you suggested and its up and running now.
"Whats wrong with the cat?" - Mrs Schrödinger




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users