Loop within a loop

[moracom]

I have this database that has 2 tables.
I have this PHP script that will pull data from table 1 and it will loop itself until it retrieves all the records that I need. The second table contains records that belong to a specific record in table 1. I thought that by scripting my first loop and then adding a loop withing the first loop that will call upon the second table will retrieve all the records from table 1 and all the records from table 2 that corresponds to the records in table 1. What happens is that the script will only pull one record from table 1 and one record from table 2 and then it dies.
It's a long script but here it is. All the help that I can get will be great.

<?
$hostname = "localhost";
	$dbName = "x";		
	$username = "xx";
	$password = "xxx";
    /* Connecting, selecting database */
    $dbh3=mysql_connect ($hostname, $username, $password)
        or die("Could not connect");
    mysql_select_db($dbName) or die("Could not select database");
    /* Performing SQL query */
    $query = "SELECT * tbl_Departments WHERE City = "Boston";
    $result = mysql_query($query) or die("Query failed pulling the data");
	// Determine the number of applicants 
	$number = mysql_numrows($result);
// Print the relevant information
print  "<table width=100% border=0 cellpadding=3 cellspacing=0>
	<tr>
		<td width=100% align=left valign=top><strong>Small</strong></td>
	</tr>
</table>";
If ($number == "0"){
print "No offices in this size.";
}else{
print "<table width=100% border=0 cellpadding=10 cellspacing=0>";
	 for($i=0; $i<$number; $i++) {
    $DepartmentID = mysql_result($result, $i,  "DepartmentID");
    $DNumberPartners = mysql_result($result, $i,  "DNumberPartners");
    $DNumberAssoc = mysql_result($result, $i,  "DNumberAssoc");
    $DNumberCounsel = mysql_result($result, $i,  "DNumberCounsel");
    $DKnownFor = mysql_result($result, $i,  "DKnownFor");
    $DTidBit = mysql_result($result, $i,  "DTidBit");
    $DTotalAttys = mysql_result($result, $i,  "DTotalAttys");
     /* print even-numbered rows with a grey background, 
           odd-numbered with a blue background */ 
        if ($i%2 == 0) {
        print  "<tr>";
        } else {
        print  "<tr>";
        }
    print  "<td width=100% align=left valign=top>";?>

<table bgcolor="#000000" width=100% border=0 cellpadding=3 cellspacing=3>
	<tr>
		<td bgcolor="#ffffff" width=100% align=left valign=top>Group has <?echo $DTotalAttys;?> attorneys with <?echo $DNumberPartners;?> partners, <?echo $DNumberAssoc;?> associates, and <?echo $DNumberCounsel;?> of counsel.</td>
	</tr>
</table>

<table bgcolor="#000000" width=100% border=0 cellpadding=3 cellspacing=3>
	<tr>
		<td bgcolor="#ffffff" width=100% align=left valign=top>
<?//Second loop to second table Starts here?>
<?$hostname = "localhost";
	$dbName = "x";		
	$username = "xx";
	$password = "xxx";
    /* Connecting, selecting database */
    $dbh=mysql_connect ($hostname, $username, $password)
        or die("Could not connect");
    mysql_select_db($dbName) or die("Could not select database");
	    /* Performing SQL query */
    $query = "SELECT * FROM tbl_AOF WHERE ADepartmentID = '$DepartmentID'";
    $result = mysql_query($query) or die("Query failed pulling the data");
	// Determine the number of applicants 
	$number = mysql_numrows($result);
// Print the relevant information
print "They do a mix of ";
	 for($i=0; $i<$number; $i++) {
    $AOFDesc = mysql_result($result, $i,  "AOFDesc");
     /* print even-numbered rows with a grey background, 
           odd-numbered with a blue background */ 
        if ($i%2 == 0) {
        print  "";
        } else {
        print  "";
        }
    print  "$AOFDesc, ";?>

<?	print	"";        

}
print  ".";?>
<?//Second loop to second table Ends here?>
		</td>
	</tr>
</table>

<?	print	"</td></tr>";        

}
print  "</table>";
} //end of the $number = "0"
    /* Free resultset */
    mysql_free_result($result);

/* Closing connection */
mysql_close($dbh);
//Small ends here?>

[eriksmoen]

you could just use the common "while" statement for your query...

$query = @mysql_query("SELECT * FROM tbl_Departments WHERE `city`='Boston';");
while($result = @mysql_fetch_array($query))
{
    $DepartmentID = $result["DepartmentID"];
    $DNumberPartners = $result["DNumberPartners"];
    $DNumberAssoc = $result["DNumberAssoc"];
    $DNumberCounsel = $result["DNumberCounsel"];
    $DKnownFor = $result["DKnownFor"];
    $DTidBit = $result["DTidBit"];
    $DTotalAttys = $result["DTotalAttys"]; 

// echo your statements here
// add another query if need be here
// much simpler and easier to read...
}

another note is it should be mysql_num_rows() instead of mysql_numrows(query).

[moracom]

But what about pulling data from the second table within the first "while" statment? Will it work? How?

[eriksmoen]

$query = @mysql_query("SELECT * FROM tbl_Departments WHERE `city`='Boston';");
while($result = @mysql_fetch_array($query))
{
    $DepartmentID = $result["DepartmentID"];
    $DNumberPartners = $result["DNumberPartners"];
    $DNumberAssoc = $result["DNumberAssoc"];
    $DNumberCounsel = $result["DNumberCounsel"];
    $DKnownFor = $result["DKnownFor"];
    $DTidBit = $result["DTidBit"];
    $DTotalAttys = $result["DTotalAttys"];

// echo your statements here

$query2 = @mysql_query("SELECT * FROM tbl_AOF WHERE `ADepartmentID` = '$DepartmentID';");
while($result2 = @mysql_fetch_array($query2))
{
       // echo your query2 statements here
}
}

[moracom]

Thanks alot eriksmoen!
It worked!

[eriksmoen]

cool, good to hear!