Fearpig Posted August 23, 2006 Share Posted August 23, 2006 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:AccountsName1Name2Name3MarketingName4ITName5Name6.....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!CheersTom Quote Link to comment Share on other sites More sharing options...
Jenk Posted August 23, 2006 Share Posted August 23, 2006 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]<?phpwhile ($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. Quote Link to comment Share on other sites More sharing options...
Fearpig Posted August 23, 2006 Author Share Posted August 23, 2006 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:IDDepartmentDescriptionTelephoneeMailtbl_telephonenumbers has the following fields:IDFirst_NameLast_NameDepartment (contains a number which matches to the ID field in tbl_department)DescriptionTelephoneeMailAs 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.