CODEWITHSUNDEEP
phpmysqldatetime
AndroidDev
AndroidDev

Reputation: 21237

MySQL Query Interpret Date String as Date

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'";

Upvotes: 1

Views: 315

Answers (2)

de_nuit
de_nuit

Reputation: 650

<?php
$wkbegin = $_POST['wk_begin'];
$wk_begin = strToTime($wkbegin);
$d=date('j', $wk_begin)+8;
$m=date('n', $wk_begin);
$y=date('Y', $wk_begin);
$wk_end = date('Y-m-d', mktime(0,0,0, $m, $d, $y));
$wkbegin = date('Y-m-d', $wk_begin);
$sql = "SELECT * FROM `myTable` WHERE `date`>='".$wkbegin."' AND `date`<'".$wk_end."'";
?>

Upvotes: 0

Orangepill
Orangepill

Reputation: 24645

date is a reserved word in mysql, to use it as a field name surround it in backticks.

Also your variable insertions need to be surround in apostrophes

SELECT * FROM myTable WHERE (DATE(`date`)>='$wk_begin' AND DATE(`date`)<'$wk_end'

A better solution (in addition to making the date field a date field) would be to use the db library's escaping and quoting mechanisms when building the sql statement.

Also since you are storing your dates in Y-m-d format the cast to date is unneccessary since string comparison on dates formated this way evaluate the same as the string. So

$sql = "SELECT * from myTable WHERE `date` >= ".$db->quoteAndEscape($wk_begin)." and `date` < ".$db->quoteAndEscape($wk_end);

UPDATE

Based on this date can be used as an unquoted field name.

Upvotes: 3

Related Questions