I have a php/mysql site that currently displays data from a database. I need to add a filter to only include data prior to April 1st. Can someone please show me the correct code? All help is sincerely appreciated.
<?php
include '../physed_dbcnx.inc.php'; // database connection code - ** variable $dbcnx **
$datetime = date('n/j/y g:i:s A');
$user_count = 0;
if (isset($_POST['filter'])){ // looking for filter request
$filter = $_POST['filter'];
if ($filter == 'Med Student') {
$qry_insert = 'WHERE assess_role.id = 5 ';
} elseif ($filter == 'Resident/Intern') {
$qry_insert = 'WHERE assess_role.id = 2 OR assess_role.id = 3 ';
} elseif ($filter == 'Staff Physician') {
$qry_insert = 'WHERE assess_role.id = 1 ';
} elseif ($filter == 'Mid-Level Provider') {
$qry_insert = 'WHERE assess_role.id = 4 ';
} else {
$qry_insert = '';
}
} else {
$filter = 'All';
$qry_insert = '';
}
echo '<form name="sorting" method="post" action="'.$_SERVER['PHP_SELF'].'">';
echo 'See: <input type="submit" name="filter" value=" All ">';
echo '<input type="submit" name="filter" value="Med Student">';
echo '<input type="submit" name="filter" value="Resident/Intern">';
echo '<input type="submit" name="filter" value="Staff Physician">';
echo '<input type="submit" name="filter" value="Mid-Level Provider">';
echo '</form>';
echo '<p> </p>';
echo '<p class="viewtitle">Assessment Report ('.$filter.') <span class=contract>'.$datetime.'<span></p>';
echo '<table>';
echo '<tr class="line0"><td>Name</td><td>Email</td><td>Role</td><td>Assessment</td><td align=right>Score</td><td>Failed</td></tr>';
$qry_users = "SELECT assess_registered_users.id, assess_registered_users.fname, assess_registered_users.lname, assess_registered_users.mi, assess_registered_users.email, assess_role.role AS role, assess_role.id AS roleid FROM assess_registered_users INNER JOIN assess_role ON assess_registered_users.roleid = assess_role.id ".$qry_insert."ORDER BY assess_registered_users.lname, assess_registered_users.fname";
$result_users = odbc_exec($dbcnx, $qry_users);
if(!$result_users) {
echo '<p class="error">Error in query of database - Users</p>';
}
while (odbc_fetch_row($result_users)){
$ruid = odbc_result($result_users, 'id');
$fname = odbc_result($result_users, 'fname');
$lname = odbc_result($result_users, 'lname');
$mi = odbc_result($result_users, 'mi');
$email = odbc_result($result_users, 'email');
$roleid = odbc_result($result_users, 'roleid');
$role = odbc_result($result_users, 'role');
$line_count = 0;
$num_passed = 0;
$qry_assess = "SELECT assessment.id AS id, assessment.title AS title, assess_scores.assessid AS aid, assess_scores.reguserid AS ruid, assess_scores.numCorrect AS correct, assess_scores.numTotal AS total, assess_scores.wrongAnswers AS wrongAnswers, assess_scores.timesTaken AS timesTaken FROM assess_scores INNER JOIN assessment ON assess_scores.assessid = assessment.id WHERE assess_scores.reguserid = '$ruid'";
$result_assess = odbc_exec($dbcnx, $qry_assess);
if(!$result_assess) {
echo '<p class="error">Error in query of database - Assessments</p>';
}
while (odbc_fetch_row($result_assess)){
$title = odbc_result($result_assess, 'title');
$correct = odbc_result($result_assess, 'correct');
$total = odbc_result($result_assess, 'total');
$wrongAnswers = odbc_result($result_assess, 'wrongAnswers');
$timesTaken = odbc_result($result_assess, 'timesTaken');
$passing = 85;
$score = round($correct / $total * 100);
$line_count++;
$r = fmod($line_count, 2);
if ($r > 0){
echo '<tr class="line1">';
} else {
echo '<tr class="line2">';
}
if ($line_count == 1) {
$user_count++;
echo '<td class=contract><span class=bold>'.strtoupper(trim($lname)).', '.strtoupper(trim($fname)).' '.strtoupper(trim($mi)).'</span></td><td class=contract>'.strtolower(trim($email)).'</td><td class=contract>'.trim($role).'</td>';
} else {
echo '<td class=contract> </td><td class=contract> </td><td class=contract> </td>';
}
echo '<td class=contract>'.trim($title).'</td><td class=contract align=right>'.$score.'</td>';
if ($score < $passing) {
echo '<td class=contract align=center><img src="../images/icon_redflag.gif" height=12 width=12></td>';
} else {
$num_passed++;
echo '<td class=contract> </td>';
}
echo '</tr>';
}
if (odbc_fetch_row($result_assess,1)){ // out of while loop, but still need to make sure user has data before printing passed #
switch ($roleid) {
case 1:
$num_per_role = 0;
break;
case 2:
$num_per_role = 11;
break;
case 3:
$num_per_role = 11;
break;
case 4:
$num_per_role = 0;
break;
case 5:
$num_per_role = 10;
break;
}
if ($num_passed >= $num_per_role) {
echo '<tr class="line3">';
} else {
echo '<tr class="line4">';
}
echo '<td class=contract> </td><td class=contract> </td><td class=contract> </td>';
echo '<td colspan=2 align=right class=contract><span class=complete>Completed '.$num_passed.' of '.$num_per_role.'</span></td>';
echo '<td class=contract> </td></tr>';
}
}
echo '</table>';
echo '<p> </p><p>'.$user_count.' Users Total</p>';
//}
// Close Connection
odbc_close($dbcnx);
?>
