Displaying Sub Results

[Fearpig]

Hi Guys,
Could anyone help me with this one.... I'm trying to display some data in the format of:

Department1
Name1
Name2
Name3

Deparment2
Name4
Name5

Department3
Name6
Name7
Name8...

I've got as far as this code:

[code]<?php

$db = mysql_connect("localhost", "root", "password");

mysql_select_db("Telephonelist",$db);

$result = mysql_query("SELECT * FROM tbl_department",$db);
$subresult = mysql_query("SELECT * FROM tbl_telephonenumbers",$db);
$db = mysql_connect("localhost", "root", "password");

mysql_select_db("Telephonelist",$db);

$result = mysql_query("SELECT * FROM tbl_department",$db);
$subresult = mysql_query("SELECT * FROM tbl_telephonenumbers",$db);

while ($row = mysql_fetch_array($result)) {
    printf("<b>%s </b><br>\n", $row["Department"]);
    while ($row2 = mysql_fetch_array($subresult)) {
        if ($row['ID'] == $row2['Department']) {
            printf("%s %s<br>\n", $row2["First_Name"], $row2["Last_Name"]);
        }
    }
    echo '<br><br>';
}
?>[/code]

But unfortunately I only get:

Department1
Name1
Name2
Name3

Department2

Department3...

It look like the subresults are only being processed on the first pass  :(. Can anyone point me in the right direction?
Cheers
Tom

[ronverdonk]

Your second fetch statement:

[code]  while ($row2 = mysql_fetch_array($subresult)) {
        if ($row['ID'] == $row2['Department']) {
            printf("%s %s<br>\n", $row2["First_Name"], $row2["Last_Name"]);
        }
[/code]
reads the entire selection within the first fetch for "Department". It prints out the matching $row['ID'] s and ingnores al the other ones. I would advise to use a JOIN so you can handle all via one select statement.
[code]SELECT t1.ID, t2.Department, t2.First_name, t2.Lastname
FROM tbl_department as t1, tbl_telephonenumbers at t2
WHERE t1.ID=t2.Department ORDER BY t1.Department[/code]

Ronald  :cool:

[Fearpig]

Thanks Ronald but I didn't want to display the data that way.... and I managed to solve it myself  ;D  (I am very chuffed!!)

All I needed to do was move the $subresult inside of the loop so that it was processed on each pass!

[code]<?php

$db = mysql_connect("localhost", "root", "password");

mysql_select_db("Telephonelist",$db);

$result = mysql_query("SELECT * FROM tbl_department",$db);
$subresult = mysql_query("SELECT * FROM tbl_telephonenumbers",$db);
$db = mysql_connect("localhost", "root", "password");

mysql_select_db("Telephonelist",$db);

$result = mysql_query("SELECT * FROM tbl_department",$db);
//move $subresult from here

while ($row = mysql_fetch_array($result)) {
    printf("<b>%s </b><br>\n", $row["Department"]);
    $subresult = mysql_query("SELECT * FROM tbl_telephonenumbers",$db);
while ($row2 = mysql_fetch_array($subresult)) {
        if ($row['ID'] == $row2['Department']) {
            printf("%s %s<br>\n", $row2["First_Name"], $row2["Last_Name"]);
}
    }
    echo '<br><br>';
}
?>[/code]