Jump to content

Archived

This topic is now archived and is closed to further replies.

Fearpig

Displaying results in groups

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.