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.

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.

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 :(

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

Member Avatar for 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..

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

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.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.