Jump to content

[SOLVED] Need help with filtering data by date

djhamer

By djhamer
in PHP Coding Help

 Share

Recommended Posts

djhamer Newbie

djhamer

Posted
Posted

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);
?>

Link to comment
Share on other sites
DarkWater Member

DarkWater

Posted
Posted

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);
?>

 

Do you have a DATETIME column in your MySQL database that has the time that the data was made? 

Link to comment
Share on other sites
craygo Member

craygo

Posted
Posted

is it a timestamp or datetime field? makes a difference

 

Datetime

$now = date("Y-m-d G:i:s", strtotime("4/1/2008"));
$qry_insert = "WHERE `datefield` < '$now' ";

 

Timestamp

$now = date("U", strtotime("4/1/2008"));
$qry_insert = "WHERE `datefield < '$now' ";

 

Ray

 

Link to comment
Share on other sites
djhamer Newbie

djhamer

Posted
  • Author
Posted

As you can see below...I put the code in right below the $filter.  This doesnt produce the desired result.

 

	<?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'];
		$now = date("Y-m-d G:i:s", strtotime("4/1/2008"));
		$qry_insert = "WHERE `datefield` < '$now' ";
		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);
?>

Link to comment
Share on other sites
craygo Member

craygo

Posted
Posted

obviously you need to change `datefield` to whatever the name of the field is that is holding your datetime. Also try echo'ing out your query before running it so you can see what it is trying to retrieve.

 

$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";
echo "<br>Query: $qry_users<br>";

 

Ray

 

Link to comment
Share on other sites
djhamer Newbie

djhamer

Posted
  • Author
Posted

So, essentially...the code will look like this???:

 

	<?php 
	include '../physed_dbcnx.inc.php'; // database connection code - ** variable $dbcnx **

	$datetime = date('n/j/y g:i:s A');
	$user_count = 0;
	$now = date("Y-m-d G:i:s", strtotime("4/1/2008"));
	$qry_insert = "WHERE `timestamp` > '$now' ";

Link to comment
Share on other sites
djhamer Newbie

djhamer

Posted
  • Author
Posted

its still not displaying the correct data.  I'm trying to pull from the table "CourseTaken" in the timestamp field.  I want it to display all entries prior to April 1.  Here is the top part of the code that I have edited.

 

<?php 
	include '../physed_dbcnx.inc.php'; // database connection code - ** variable $dbcnx **

	$datetime = date('n/j/y g:i:s A');
	$user_count = 0;
	$now = date("Y-m-d G:i:s", strtotime("4/1/2008"));
	$qry_insert = "WHERE `CourseTaken.timestamp` > '$now' ";

	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 = '';
	}

Link to comment
Share on other sites
djhamer Newbie

djhamer

Posted
  • Author
Posted

This is now what it looks like:

 

<?php 
	include '../physed_dbcnx.inc.php'; // database connection code - ** variable $dbcnx **

	$datetime = date('n/j/y g:i:s A');
	$user_count = 0;
	$now = date("m/j/Y g:i:s A", strtotime("4/1/2008"));
	$qry_insert = "WHERE `CourseTaken.timestamp` > '$now' ";

	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 = '';
	}

Link to comment
Share on other sites
craygo Member

craygo

Posted
Posted

Well in order to work with access you have to use "#" surrounding your date

 

<?php 
	include '../physed_dbcnx.inc.php'; // database connection code - ** variable $dbcnx **

	$datetime = date('n/j/y g:i:s A');
	$user_count = 0;
	$now = date("m/j/Y", strtotime("4/1/2008"));  // can change this
	$qry_insert = "WHERE `CourseTaken.timestamp` < #$now# ";  // add the "#" here and "<" is prior

	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 = '';
	}

 

Ray

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.