Jump to content


Photo

Displaying results in groups


  • Please log in to reply
2 replies to this topic

#1 Fearpig

Fearpig
  • Members
  • PipPipPip
  • Advanced Member
  • 195 posts

Posted 23 August 2006 - 02:57 PM

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.)


<?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>';
}

?>

Any help would be appreciated!
Cheers
Tom
"Whats wrong with the cat?" - Mrs Schrödinger

#2 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 23 August 2006 - 03:22 PM

depends on your database layout.

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

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

otherwise you can add a challenge to your loop:

<?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>';
}
?>
must have a foreign key though.

#3 Fearpig

Fearpig
  • Members
  • PipPipPip
  • Advanced Member
  • 195 posts

Posted 23 August 2006 - 03:39 PM

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:

<?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>';
}
?>

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.


"Whats wrong with the cat?" - Mrs Schrödinger




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users