correct way to nest foreach ?

[ejwf]

Hello
I want to do several foreach loops to refine my queries but I am having difficulty getting exactly what I want ......

// query to list companies that have results due this month
$month = date('m');
$query = "SELECT Company FROM tblDate WHERE Month = '$month'";
$result = mysql_query($query) or die (mysql_error());
while($row = mysql_fetch_array($result))
$Company[] = $row["Company"];

//then foreach of these companies list their departments 
foreach ($Company as $ref)
{
$query = "SELECT Dept FROM tblCompany WHERE Company = '".$ref."' ";
$result1 = mysql_query($query) or die (mysql_error());
while($row1 = mysql_fetch_array($result1))
$Dept[] = $row1["Dept"];

//then foreach of these departments list the staff
foreach ($Dept as $value)
{
$query = "SELECT Name FROM tblDept WHERE Dept = '".$value."' ";
$result2 = mysql_query($query) or die (mysql_error());
while($row2 = mysql_fetch_array($result2))
$Name[] = $row2["Name"];
}

}

using these print_r () to test how the information works i get

Array ( [0] => 2 )
Array ( [0] => 353 [1] => 354 )
Array ( [0] => 202 [1] => 203 [2] => 204 )
Array ( [0] => 202 [1] => 203 [2] => 204 [3] => 199 [4] => 200 )

the first array is right, only one company due to post results this month
the second arrray is also right, 2 departments for that one company
the third array is also OK, 3 members of staff for the first department
BUT the fourth array shows the 3 members of staff from the first dept. PLUS the 2 members from the second

Why is it doing this, I need to keep the information seperate.
Can anyone see how I can correct this so that the foreach statement work correctly

TIA

[lesmith]

Hmm

You need to build you []array before you loop it.

In your 2 query you are creating another array for depots. you need to colletc that data before you can loop it again.

// query to list companies that have results due this month 
$month = date('m'); 
$query = "SELECT Company FROM tblDate WHERE Month = '$month'"; 
$result = mysql_query($query) or die (mysql_error()); 
while($row = mysql_fetch_array($result)) 
$Company[] = $row["Company"]; 

//then foreach of these companies list their departments 
foreach ($Company as $ref) 
{ 
$query = "SELECT Dept FROM tblCompany WHERE Company = '".$ref."' "; 
$result1 = mysql_query($query) or die (mysql_error()); 
while($row1 = mysql_fetch_array($result1)) 
$Dept = $row1["Dept"]; 
}
//then foreach of these departments list the staff 
foreach ($Dept as $value) 
{ 
$query = "SELECT Name FROM tblDept WHERE Dept = '".$value."' "; 
$result2 = mysql_query($query) or die (mysql_error()); 
while($row2 = mysql_fetch_array($result2)) 
$Name = $row2["Name"]; 
} 

[ejwf]

thank you for this
but changing where I close the second foreach (moving the } ) hasn't changed anything I still get the same output

any ideas

thanks

[lesmith]

A better way to carry out this operation is to do JOIN in mysql.

Look at http://dev.mysql.com/doc/mysql/en/join.html at there site.

Or do a search on Join / Inner Joins / Outer Joins on google. You will get plenty of examples on there.

IF not in your code try this.

// query to list companies that have results due this month 
$month = date('m');  

$query = "SELECT Company FROM tblDate WHERE Month = '$month'";  

$result = mysql_query($query) or die (mysql_error());  

while($row = mysql_fetch_array($result))  

$Company[] = $row["Company"];  

//then foreach of these companies list their departments 
foreach ($Company as $ref)  

{  

$query = "SELECT Dept FROM tblCompany WHERE Company = '".$ref."' ";  

$result1 = mysql_query($query) or die (mysql_error());  

while($row1 = mysql_fetch_array($result1))  

$Dept[] = $row1["Dept"];  

} 
//then foreach of these departments list the staff 
foreach ($Dept as $value)  

{  

$query = "SELECT Name FROM tblDept WHERE Dept = '".$value."' ";  

$result2 = mysql_query($query) or die (mysql_error());  

while($row2 = mysql_fetch_array($result2))  

$Name[] = $row2["Name"];  

}

I missed out the [] after the Variable.

Hope this helps

Regards

Lee