Interval part of MYSQL Query not working

[hm9]

I have 2 mysql queries on a PHP page:

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






any ideas why the interval is not working?


thanks in advance

[ScallioXTX]

It doesn't work because you're selecting it.
You should use in in a where, like

SELECT
  destination, departureDate, DATE_FORMAT(departureDate, '%d/%m/%Y') AS formattedDate
FROM
  Entries
WHERE
  departureDate > DATE_ADD(CURDATE(), INTERVAL 7 DAY)

[r937]

i find it easier to write the syntax without resorting to the DATE_ADD function

e.g. WHERE departureDate > CURRENT_DATE + INTERVAL '7' DAY

(note: i don't normally put quotes around numeric constants, but in this case that's the actual SQL standard, and of course mysql supports it)

[ScallioXTX]

e.g. WHERE departureDate > CURRENT_DATE + INTERVAL '7' DAY

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 ...)

[hm9]

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?

[r937]

please show your exact latest query

i think you might have forgotten something in the WHERE clause

[hm9]

Ok this the full query:


$result = mysql_query("SELECT destination, departureDate DATE_FORMAT(departureDate, '%d/%m/%Y') as formattedDate from entries WHERE departureDate > CURRENT_DATE + INTERVAL '7' DAY
");

Thanks

[ScallioXTX]

CURRENT_DATE() is a function, you forgot the paretheses.

[hm9]

Sorry it s still dsplaying everything.

Note the departureDate is formatted as formattedDate
and the date value selected by the user is stored in $dep' varaible

[r937]

CURRENT_DATE() is a function, you forgot the paretheses.

CURRENT_DATE is a (SQL standard) reserved word, you don't need the parentheses

[r937]

Ok this the full query:

that query will not run, because it contains a syntax error

did you test it outside of php?

also, you said "and the date value selected by the user is stored in $dep' varaible"

but the query does not reference this variable

so what's really going on here?

[hm9]

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 ?

[r937]

two problems

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...

[hm9]

Ok I see your point but need help sorting it:

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


thanks again

[r937]

using this method, means i dont have to do the date format conversion on the sql query and the interval will work ?

yes

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

the interval examples so far in this thread have used CURRENT_DATE but i'm not sure which interval to construct for the user-entered date

[hm9]

[QUOTE=r937;4530236]yes

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'

does this make sense?

[r937]

does this make sense?

sort of

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

with '$dep' formatted to year-month-day sequence

[guelphdad]

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.