Displaying results in groups

[Fearpig]

Hi Guys,
Could anyone help me with grouping my MySQL results? I have a table of names and departments and would like to present the data as it is layed out below:


Accounts
Name1
Name2
Name3

Marketing
Name4

IT
Name5
Name6.....


Heres what I've got so far... it doesn't work as yet, it displays a list of all departments with all of the people listed under the first department (I expected this to list all people under every department.)


[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);

while ($myrow = mysql_fetch_array($result)) {

printf("%s <br>\n", $myrow["Department"]);
while ($myrow = mysql_fetch_array($subresult)) {
//how do I limit the names to only the ones that match the department?
printf("%s %s<br>\n", $myrow["First_Name"], $myrow["Last_Name"]);
//for some reason it only lists the names under the first department
}
echo '<br><br>';
}

?>
[/code]

Any help would be appreciated!
Cheers
Tom

[Jenk]

depends on your database layout.

If you have a foreign key for Department name(s) on tbl_telephone numbers, then you can [code]SELECT *
FROM `tbl_department`
JOIN `tbl_telephonenumbers`
ON `tbl_department`.`Department` = `tbl_telephonenumbers`.`Department`
ORDER BY `tbl_department`.`Department`, `tbl_telephonenumbers`.`last_name` ASC[/code]

Or just have:
[code]SELECT *
FROM `tbl_telephonenumbers`
ORDER BY `Department`, `Last_name` ASC;[/code]

otherwise you can add a challenge to your loop:

[code]<?php
while ($row = mysql_fetch_array($result)) {
    printf("%s <br>\n", $row["Department"]);
    while ($row2 = mysql_fetch_array($subresult)) {
        if ($row['Department'] == $row2['Department']) {
            printf("%s %s<br>\n", $row2["First_Name"], $row2["Last_Name"]);
        }
    }
    echo '<br><br>';
}
?>[/code]must have a foreign key though.

[Fearpig]

Hi Jenk,
The code at the bottom seems to be what I am looking for but I'm not sure that I follow it. Is a Foreign Key the same as a Primary Key?

If so the database has the following set up:

tbl_department has the following fields:
ID
Department
Description
Telephone
eMail

tbl_telephonenumbers has the following fields:
ID
First_Name
Last_Name
Department (contains a number which matches to the ID field in tbl_department)
Description
Telephone
eMail

As such I've modified the code as follows:

[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 this is listing all of the departments but only listing people in the first department....
Any ideas?

Thanks for the start though thats got me well underway.