Getting records between 2 dates someone chooses php mysql
I'm trying to get records between 2 dates. The dates are entered in the form "yyyy-mm-dd" into a input type="text" (so for example I would type "2011-02-15" into a text input) and then posted to the next page which has the query:
$start = $_POST["start"];
$end = $_POST["end"];
$sql_query = "SELECT * FROM actionlist WHERE date>='{$start} 00:00:00' AND date<='{$end} 23:59:59' ORDER BY id";
$result = mysql_query($sql_query);
开发者_开发技巧The records in the table "actionlist" have a field called "date" and this is entered automatically when creating the record by using "$date = date('Y-m-d H:i:s')".
Anyway, I can't seem to get any records to be selected. Do I need to process my $start and $end variables somehow? Thanks in advance.
You can use between to get the information you want
$sql_query = "SELECT * FROM actionlist WHERE date BETWEEN '$start' AND '$end' ORDER BY id";
But you need to make sure start and end are valid, and escaped,etc.. to prevent sql injection, and the like
$start = mysql_real_escape_string(trim($_POST["start"]));
$end = mysql_real_escape_string(trim($_POST["end"]));
$sql_query = "SELECT * FROM actionlist WHERE date BETWEEN '".$start." 00:00:00' AND '".$end." 23:59:59' ORDER BY id";
$result = mysql_query($sql_query) or die(mysql_error());
Edit: the trim() in case you have some unwanted space in your input forms; a bit of sanitation then, and use BETWEEN, as everyone has suggested right before me (damn I'm so slow at writing...)
Edit 2 based on comment below
Try that way
SELECT * FROM actionlist WHERE date BETWEEN 'start' AND 'end'
精彩评论