I saw a couple of posts on this problem, but I'm not getting it to work correctly. I have a PHP variable that I initially POST as a string. To be able to handle a one week date range, I am converting this using strToTime into datetime format (e.g. July 22, 2013 echos as 1374476400).
My table has its dates stored as text in a Y-m-d format (e.g. July 22, 2013 is stored as 2013-07-22). I need to run a query comparing these two values to find dates that fall within my one week range, so I am trying to convert that text into datetime format for comparison purposes.
Here is the SQL that I am using:
$wk_begin = $_POST['wk_begin'];
$wk_begin = strToTime($wk_begin);
$wk_end = $wk_begin + 8;
$sql = "SELECT * FROM myTable WHERE (DATE(date)>=$wk_begin AND DATE(date)<$wk_end)";
I'm not getting any errors, but my query isn't picking any records up, even though i know that there are 7 matching records in the table. So I have two questions:
1) Is there a better way to go about this? 2) If I am on the right track, how can I get the sql statement to convert the text based dates into a big integer so that I can do the comparison correctly.
Any help is appreciated. Thanks!
EDIT: Thanks for all of the suggestions. I've changed the table so that the dates are stored in 'date' format (no more strings). Here is the updated code. Still not picking up any values:
$wk_begin = $_POST['wk_date'];
$wk_end = $wk_begin + 7;
$sql = "SELECT * FROM Workouts WHERE 'date' BETWEEN '$wk_begin' AND '$wk_end'";