<?php
$con = mysql_connect("server","user","pass");
 
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}
 
mysql_select_db("database", $con);
 
//IF THE FLAG HASN'T BEEN SET YET, SET THE DEFAULT
if(!isset($_GET['orderBy'])) {
     $_GET['orderBy'] = 'FRSTNAME';
}
 
//FIGURE OUT HOW TO SORT THE TABLE
switch($_GET['orderBy']) {
     case 'FRSTNAME':
     case 'LASTNAME':
    case 'ADDRESS3':
    case 'STRINGS_I_3':
    case 'MSTRING_I_5':
    case 'DepartmentName':
          $sql_orderBy = $_GET['orderBy'];
          break;
 
     default:
          $_GET['orderBy'] = 'FRSTNAME';
          $sql_orderBy = 'FRSTNAME';
}
 
 //FILL THE DROPDOWN WITH DEPARTMENTS
 $options .= "<option value='' name='department'><b>Filter by Department</b></option>";
 $sql = "select distinct DepartmentName from EmpDirFull ORDER BY DepartmentName ASC";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    $options .="<option value='".$row['DepartmentName']."'";
   if ($row['DepartmentName']  == $_POST['department']) {
   $options .=" selected='selected'>".$row['DepartmentName']."</option>";
   } else {
   $options .=">".$row['DepartmentName']."</option>";
   }
}
 
$departmentsDropDown="<form method='post' action=''>
    <select name='department' onchange='this.form.submit();'>
      " . $options . "
    </select>
   </p>
</form>";
 
//GET THE LIST OF STAFF INFO
$empDirList = '';
if (empty($_POST['department'])) {
   $sql_filterBy = 'IS NOT NULL';
}else{
   $sql_filterBy = '= ' . $_POST['department'];
}
   $sql = "SELECT * FROM Table WHERE DepartmentName $sql_filterBy ORDER BY $sql_orderBy";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
     $empDirList .= '<tr><td>' . htmlentities($row['FRSTNAME']) . '</td><td>' . htmlentities($row['MIDLNAME']) . '</td><td>' . htmlentities($row['LASTNAME']) . '</td><td><a href="mailto:' . htmlentities($row['ADDRESS3']) . '">' . htmlentities($row['ADDRESS3']) . '</a></td><td>' . htmlentities($row['STRINGS_I_3']) . '</td><td>' . htmlentities($row['MSTRING_I_5']) . '</td><td>' . htmlentities($row['DepartmentName']) . '</td></tr>';
}
 
//DISPLAY THE CONFERENCE REGISTRANTS
print '<div id="staff-directory">';
print $departmentsDropDown;
print '<table cellpadding="0" cellspacing="1">';
print '<tr>';
print '<th scope="col">';
print '<a href="/staff_directory?orderBy=FRSTNAME">First</a>'; 
print '</th>';
print '<th scope="col">';
print 'M.'; 
print '</th>';
print '<th scope="col">';
print '<a href="/staff_directory?orderBy=LASTNAME">Last</a>'; 
print '</th>';
print '<th scope="col">';
print '<a href="/staff_directory?orderBy=ADDRESS3">Email</a>'; 
print '</th>';
print '<th scope="col">';
print '<a href="/staff_directory?orderBy=STRINGS_I_3">Credentials</a>'; 
print '</th>';
print '<th scope="col">';
print '<a href="/staff_directory?orderBy=MSTRING_I_5">Title</a>'; 
print '</th>';
print '<th scope="col">';
print '<a href="/staff_directory?orderBy=DepartmentName">Dept.</a>'; 
print '</th>';
print '</tr>';
print $empDirList;
print '</table>';
print '</div>';
 
mysql_close($con);
?>