The first query just checks the exact match of a departure date and returns the exact results. this works as expected:
$result = mysql_query("SELECT destination, departureDate
DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from Entries where DATE_FORMAT(departureDate, ‘%d/%m/%Y’) = ‘$dep’ ");
The second query will only be excecuted if there are no exact entries, that s why i am using the interval so that it can check alternative dates within 7 days for example, rather than say nothing is found. but at the moment, this returns lot of entries beyond the 7 day interval. It should only display the entries within the interval day set. the query is shown below:
$result = mysql_query(“SELECT destination, departureDate, DATE_FORMAT(Date_Add(departureDate, interval 7 day), ‘%d/%m/%Y’) as formattedDate from Entries”);
Didn’t know you could do that. Nice one, much more readable.
It’s also two functions less to worry about whether they were with an underscore or not.
(DATE_ADD and DATE_SUB, among others, have underscores, DATEDIFF does not, it’s like the $needle, $haystack problem in PHP …)
Thanks Guys.
I tried to implement this option but it just returns everything. regardless of what i set in the interval part
WHERE CURRENT_DATE + INTERVAL ‘7’ DAY
Note. The variable ‘$dep’ is what is actually date selected by the user from the php form so it should add interval to it.
The other point is that on mysql query I have formatted the date using DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate. Which converts the mysql date fromat from yyyy-mm-dd to dd-mm-yyyy, so may be this needs to be taken into account?
$result = mysql_query("SELECT destination, departureDate DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from entries WHERE departureDate > CURRENT_DATE + INTERVAL ‘7’ DAY
");
Ok. If I run this query it works and returns the correct result;
$result = mysql_query("SELECT destination, departureDate
DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from Entries where DATE_FORMAT(departureDate, ‘%d/%m/%Y’) = ‘$dep’ ");
As you can see the variable is contained within the where clause and stores whichever date the user selects. Hence it works = it returns the entries that matches the date selected by the user.
but If I add interval by using the following query, it does not work obviously because it needs the varaible ‘$dep’ to add interval to it :
$result = mysql_query("SELECT destination,departureDate, DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate from Entries WHERE departureDate > CURRENT_DATE() + INTERVAL ‘7’ DAY
");
but I am not sure how to add variable ‘$dep’ in the where clause ?
first, by reformatting the departuredate column in the WHERE clause, the optimizer cannot use an index on that column, it must use a table scan, so your query will not scale, it will get slower and slower the more rows you have
the solution is to use STR_TO_DATE on the ‘$dep’ variable to get it into the correct year-month-day sequence, or better yet, format it correctly using php before calling the query
the second problem is that you haven’t adequately explained which dates you’re actually trying to retrieve based on the user-entered date…
The date that is stored in the variable ‘$dep’ has this format: dd-mm-yyyy.
The date in the mysql table is stored in this format: yyyy-mm-dd.
So need to convert the ‘$dep’ into mysql format in php before querying the database. but then I need it back in the uk format dd-mm-yyyy to display to the user.
using this method, means i dont have to do the date format conversion on the sql query and the interval will work ?
Could you please advise on how i need to modify the query
but please note you still haven’t explained how you want the user-entered date to be compared to the stored date in the table
Ok If a user enters a date like 13/03/2010 then I want to return the entries that are available within 7 days interval from that date. for example: entries between 12/03/2010 and 20/03/2010
so need something like select the entries where the dates in the table are within 7 days interval of the date selected by the user in the form of the variable ‘$dep’
the 12th through the 20th is actually 8 days (or 9, if you count both ends of the range)
so you want “departures for the user-entered date and up to 7 days thereafter” (this is what i meant by an explanation – who knows, you could’ve meant 7 days before, or both 7 days before and 7 days after)
and i presume today’s date is no longer relevant
WHERE departuredate >= '$dep'
AND departuredate < '$dep' + INTERVAL '7' DAY
Yes that is correct: “departures for the user-entered date and up to 7 days thereafter”
This is exactly what I am trying to achieve.
now I need to format the $dep variable into yyyy-mm-dd. not sure how as it is retrieved from the form the user submit as: $dep= $_GET[“departuredate”] and the date can be printed correctly as dd-mm-yyyy but obviously need to be converted to the other format yyyy-mm-dd
Before I test the interval part , I want to check that the first part of the query works as shown below:
WHERE departureDate >= DATE_FORMAT( $dep, ‘%Y/%m/%d’)
unfortunately, this query displays all entries regardless of the date the user selects. As you can see I tried to format the $dep to the yyyy-mm-dd in the where clause so it can query the mysql table, but it doesnt work. it just displays all entries and dispaly them as yyyy-mm-dd.
the second task would be to convert the date back to dd-mm-yyyy so it can be displayed in the UK format.
once this works then I ll try the interval part of the query
Am i missing something?
stop worrying about formatting your date altogether. get your query working correctly first and then add the date_format to the SELECT clause only, not the WHERE part.