[SOLVED] Help with dates and dates datatype with sql and php.

[Eggzorcist]

I'm trying to create a query where it matches both the username and the date - so that if the date is 5 days away or shorter it doesn't show, but if it's 6 days or more away it will show it those queries.

 

My sql field uses the date datatype and a example of that field is: "2009-08-12"

 

Here's my function and query...

$filtertime = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+5, date("Y")));

$query = "SELECT * FROM events WHERE user = '".$user."' and fromslashes > '.$filtertime.'"

 

The problem is that it shows all where the user = $user and disregards the fromslahes > than $filtertime...

 

Any ideas?

[Psycho]

Look at your query a little closer. You are not appending $filtertime to the query; the periods are within the double quotes that you are using to define your query. The result would look something like this

SELECT * FROM events WHERE user = '[username]' and fromslashes > '.[filtertime].'

 

If you were to have done a simple debug to echo the query out you would have found the error. When using double quotes to define a string there is no need to exit the quote to append a variable, but I like to encapsulate

 

$filtertime = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")+5, date("Y")));

$query = "SELECT * FROM events WHERE user = '{$user}' and fromslashes > '{$filtertime}";

[fooDigi]

this may help... here's an idea that i use and it is not adjusted for your code... i pull records from the last 5 days using this, you may be able to tune it to your needs...

 

where DATE_FORMAT(DateField,"%Y-%m-%d") >= DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY)

[Eggzorcist]

Perfect, thanks to you both! Will know this for my current and future applications. Thanks a lot to you both.