Jump to content

Displaying results in groups


Fearpig

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
Link to comment
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.
Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.