I have a sortable table which displays :
Employee - Time in Service - Class - Department - Position
It displays as :
Employee by name - # months - Pay Class - Department - Position
Next Employee by name
Next Employee by name
Next Employee by name
I would like to make the table so it is sortable either by names as previously indicated or with supervisor in a category with those under their supervision grouped together.
Supervisor/Employee - Time in Service - Class - Department - Position
Supervisor by name
-- Employee by name
-- Next Employee by name
-- Next Employee by name
-- Next Employee by name
Next Supervisor by name
-- Next Employee by name
-- Next Employee by name
-- Next Employee by name
-- Next Employee by name
-- Next Employee by name
-- Next Employee by name
This is what I have:
// Number of records to show per page:
$display = 50;
// Determine how many pages there are.
if (isset($_GET['np'])) { // Already been determined.
$num_pages = $_GET['np'];
} else { // Need to determine.
// Count the number of records
$query = "SELECT COUNT(*) FROM employees ORDER BY employee_name ASC";
$result = @mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$num_records = $row[0];
// Calculate the number of pages.
if ($num_records > $display) { // More than 1 page.
$num_pages = ceil ($num_records/$display);
} else {
$num_pages = 1;
}
} // End of np IF.
// determine where in the database to start returning results
if (isset($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}
// default column links.
$link1 = "{$_SERVER['PHP_SELF']}?sort=ena";
$link2 = "{$_SERVER['PHP_SELF']}?sort=ela";
$link3 = "{$_SERVER['PHP_SELF']}?sort=eca";
$link4 = "{$_SERVER['PHP_SELF']}?sort=era";
$link5 = "{$_SERVER['PHP_SELF']}?sort=epa";
// Determine the sorting order.
if (isset($_GET['sort'])) {
// Use existing sorting order.
switch ($_GET['sort']) {
case 'tna':
$order_by = 'employee_name ASC';
$link1 = "{$_SERVER['PHP_SELF']}?sort=end";
break;
case 'end':
$order_by = 'employee_name DESC';
$link1 = "{$_SERVER['PHP_SELF']}?sort=ena";
break;
case 'ela':
$order_by = 'employee_timeinservice ASC';
$link2 = "{$_SERVER['PHP_SELF']}?sort=eld";
break;
case 'eld':
$order_by = 'employee_timeinservice DESC';
$link2 = "{$_SERVER['PHP_SELF']}?sort=ela";
break;
case 'eca':
$order_by = 'employee_class ASC';
$link3 = "{$_SERVER['PHP_SELF']}?sort=ecd";
break;
case 'ecd':
$order_by = 'employee_class DESC';
$link3 = "{$_SERVER['PHP_SELF']}?sort=eca";
break;
case 'era':
$order_by = 'employee_department ASC';
$link4 = "{$_SERVER['PHP_SELF']}?sort=erd";
break;
case 'erd':
$order_by = 'employee_department DESC';
$link4 = "{$_SERVER['PHP_SELF']}?sort=era";
break;
case 'epa':
$order_by = 'employee_position ASC';
$link5 = "{$_SERVER['PHP_SELF']}?sort=epd";
break;
case 'epd':
$order_by = 'employee_position DESC';
$link5 = "{$_SERVER['PHP_SELF']}?sort=epa";
break;
}
// $sort will be appended to the pagination links
$sort = $_GET['sort'];
} else { // use the default sorting order.
$order_by = 'employee_name ASC';
$sort = 'ena';
}
// make the query.
$query = "SELECT employee_name, employee_timeinservice, employee_class, employee_department, employee_position, employee_id FROM employees ORDER BY $order_by LIMIT $start, $display";
$result = @mysql_query ($query); // Run the query.
// table header
echo '<table align="center" cellspacing="0" cellpadding="5">
<tr>
<td align="left"><b><a href="'.$link1.'">Employee</a></b></td>
<td align="left"><b><a href="'.$link2.'">Time In Service</a></b></td>
<td align="left"><b><a href="'.$link3.'">Class</a></b></td>
<td align="left"><b><a href="'.$link4.'">Department</a></b></td>
<td align="left"><b><a href="'.$link5.'">Position</a></b></td>
</tr>
';
// fetch and print all records
$bg = '#eeeeee'; // set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.
echo '<tr bgcolor="' . $bg . '">
<td align="left">' . $row['employee_name'] . '</td>
<td align="left">' . $row['employee_timeinservice'] . '</td>
<td align="left">' . $row['employee_class'] . '</td>
<td align="left">' . $row['employee_department'] . '</td>
<td align="left">' . $row['employee_position'] . '</td>
</tr>
';
}
echo '</table>';
mysql_free_result ($result); // Free up the resources.
mysql_close(); // Close the database connection.
// Make the links to other pages, if necessary.
if ($num_pages > 1) {
echo '<br /><p>';
// Determine what page the script is on.
$current_page = ($start/$display) + 1;
// If it's not the first page, make a Previous button.
if ($current_page != 1) {
echo '<a href="roster.php?s=' . ($start - $display) . '&np=' . $num_pages . '&sort=' . $sort .'">Previous</a> ';
}
// Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $current_page) {
echo '<a href="roster.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '&sort=' . $sort .'">' . $i . '</a> ';
} else {
echo $i . ' ';
}
}
// If it's not the last page, make a Next button.
if ($current_page != $num_pages) {
echo '<a href="roster.php?s=' . ($start + $display) . '&np=' . $num_pages . '&sort=' . $sort .'">Next</a>';
}
echo '</p>';
}
