Forum Moderators: coopster
I have a database with various members, each belonging to a department. I need to sort the list by department, giving the title of the department in a line of the table, followed by the members of the department each as lines of the table. I'm using PHP and MySql.
When there are no more members in a department, I need to then move onto the next department, and list the staff there, and so on until all departments and staff have been listed.
Here's a piece of code that lists all of the staff in the directory:
while($myrow = mysql_fetch_array($result)){
$newID = $myrow['0'];
$newFirstName = $myrow['1'];
$newSurname = $myrow['2'];
$newState = $myrow['3'];
$newTelephone = $myrow['4'];
$newDirectLine = $myrow['5'];
$newMobile = $myrow['6'];
$newFax = $myrow['7'];
$newEmail = $myrow['8'];
$newDepartment = $myrow['9'];
$newJobTitle = $myrow['10'];
$newCarReg = $myrow['11'];
$newPicture = $myrow['12'];
$newLocation = $myrow['13'];
$newImportance = $myrow['14'];
$displayblock3 .= "
<tr><td valign='top'>
<p class='DarkFont'><a href='mailto:$newEmail' class='DarkFontSmall'>$newFirstName $newSurname</a></td>
<td valign='top'><p class='DarkFontSmall'>$newJobTitle</p></td>
<td valign='top'><p class='DarkFontSmall'>$newDirectLine</p></td>
<td valign='top'><p class='DarkFontSmall'>$newTelephone</p></td>
<td valign='top'><p class='DarkFontSmall'>$newFax</p></td>
<td valign='top'><p class='DarkFontSmall'>$newMobile</p></td>
</tr><tr><td colspan='6'><img src='images/spacer-black.gif' width='100%' height='1'></td></tr>
";
}
And here is a piece of code that lists all of the departments available:
while($myrow2 = mysql_fetch_array($result2)){
$newDept = $myrow2['0'];
print"<table border='0' width='650' align='center' cellpadding='2' cellspacing='0' bgcolor='#FFFFFF' class='border-black'>\n";
print"<tr bgcolor='#90A7D6'>\n";
print"<td colspan='6'><p class='whitefont'><b>$newDept</b></p></td>\n";
print"</tr>\n";
Any help would be appreciated!
Thanks
I need to sort the list by department, giving the title of the department in a line of the table, followed by the members of the department each as lines of the table
Try something like this. In your SQL query, order by department, then name or whatever you wish. Then use a flag to indicate when you've reached the next department in the list. Example:
$lastDepartment = "";
while($myrow = mysql_fetch_array($result)) {
$newFirstName = $myrow['1'];
$newSurname = $myrow['2'];
$newDepartment = $myrow['9'];
if ($newDepartment!= $lastDepartment) {
print "Department: $newDepartment<br>";
}
print "Employee: $newFirstName $newSurname<br>"
$lastDepartment = $newDepartment;
}
