Select Statement from withing PHP where dates are equal...

[rmerry]

Hi all,

I'm really struggling with this and so far haven't been able to find a solution on the Internet. I have a MySQL table called Journey which has a Date field. The dates are stored in the format YYYY-MM-DD. I'm trying to construct a query where I can get all JourneyIDs matching a specific date so what I do first is to create a date object in PHP:

$searchDate = mktime(0,0,0,4,18,2011);

I then create the query converting the date object to the same format as stored in the database:

$query="SELECT JourneyID FROM Journey WHERE 'Date' = " . date("Y-m-d", $searchDate);

if ($records=@mysql_query($query)) { ...

This never returns anything, even though with some dates I patch into it, I know there are multiple records.

Please help me, what am I doing wrong?

Thanks a lot.

[kekkaishi]

Is Date covered in single quotes? If so it should not be.

$query="SELECT JourneyID FROM Journey WHERE 'Date' //no single quotes

However, . date("Y-m-d", $searchDate) should be covered in single quotes. i. e.

$query="SELECT JourneyID FROM Journey WHERE `Date` = '" . date("Y-m-d", $searchDate)."'";

Hope this solves your issue.

[rmerry]

Hi there, I've just tried it many different ways but nothing is working for me:

$query="SELECT FlightID FROM Flight WHERE Date = 'date(\"Y-m-d\", $theTime)'";
$query="SELECT FlightID FROM Flight WHERE Date = 'date(Y-m-d, $theTime)'";
$query="SELECT FlightID FROM Flight WHERE Date = \"date('Y-m-d', $theTime)\"";

I tried all of the above and still no joy :(

[Jiwe]

Check if the date function is returning the exact date as it is in the database

[TechySafi]

echo $test=date("Y-m-d", $searchDate);

see if returned date is alright....if it is then simply put that variable inside the query..

[rmerry]

Just want to thank all you guys for your suggestions - I've finally worked it out. It turns out that the date field stored in the database needs to be enclose in the Date() function like so:

$query="SELECT JourneyID FROM Journey WHERE DATE(Date) = '".date('Y-m-d', $searchDate)."'";

I hope this helps somebody else in the future.

All the best,
Rich

[kekkaishi]

Just out of curiosity.. Did you have your Date field defined as a type 'DATE' in the first place?
On another note, it is good practice not to use mysql keywords while naming your tables or columns for that matter.

[rmerry]

Hi kekkaishi,

Yeah the field is of type Date. I guess you're right about the keyword thing, it didn't occur to me before.