[SOLVED] where clause to sort on date column

[wrave]

I am pretty new to PHP and have to say that I have accomplished more in a year than I ever have using any other language. PHP is great and thanks to all who help in the development of this tool.

 

I have a simple MySQL DB set up to store scheduling information. Here is my query...

 

SELECT * FROM schedule WHERE appt_date >= $this_day AND id = $this_id ORDER BY appt_date

 

Dates are in MySQL format (YYYY-MM-DD).

 

I want all dates that are in the future from $this_day. I get records from the past as well as the future.

 

I've read docs on DATEs but must be missing something.

 

Can anyone offer any help?

 

Thanks...

 

WRAVE

[rajivgonsalves]

this should work

 

SELECT * FROM schedule WHERE to_days(appt_date) >= to_days(now()) AND id = $this_id ORDER BY appt_date

[aschk]

If i'm not mistaken you've not put in the qualifying inverted commas ('), so i expect your statement is currently coming out something like this :

(example : date is 2004-12-23 and id is 34)

SELECT * FROM schedule WHERE appt_date >= 2004-12-23 AND id = 34 ORDER BY appt_date

 

Now as far as SQL is concerned what you've provided for it there is 2004 MINUS 12 MINUS 23 which gives it 1969, so really your statement looks like :

SELECT * FROM schedule WHERE appt_date >= 1969 AND id = 34 ORDER BY appt_date

 

I am however surprised that it's not causing a warning or error.

Nevertheless appt_date is ALWAYS greater than 1969 (because the condition evaluates to true).

 

[aschk]

Just done a little test myself and MySQL DOES throw a warning when you do this evaluation.

I got "Incorrect datetime value: '1969' for column 'console_Stamp' at row 1".

I suspected it might give me something along those lines.

 

Anyhow your statement should be either  :

 

1) SELECT * FROM schedule WHERE appt_date >= CURRENT_TIMESTAMP AND id = $this_id ORDER BY appt_date

2) SELECT * FROM schedule WHERE appt_date >= '$this_day' AND id = $this_id ORDER BY appt_date

 

Note that $this_day should ideally be a string in the format 'YYYY-MM-DD' as you said in the original post.

e.g. $this_day = '2007-11-14';

 

p.s. CURRENT_TIMESTAMP is SQL standard, whereas NOW() is NOT...

[wrave]

Rajiv, that works!!!

 

I had not run into the docs on the "to_days()" function yet. There is so much to read and learn!

 

I have the following code, clipped from somewhere immediately following the execution of the query...

 

if (!$r) {

  echo "<b>Query failed:</b> ".mysql_error()."<br />\n";

  echo "<b>Query:</b> $q<br />\n";

}

 

But I do not get a warning and without checking my .ini file, I am pretty sure I have warnings turned on.

 

aschk - I see what you are saying.

 

Thanks to the both of you. Wow! That was FAST help.

 

I'll be back again.

 

TTFN...

 

[aschk]

Just to follow up on this.

The reason you're not getting a mysql_error() is that it ISN'T an error. Your query worked (i.e. didn't fail), it just threw a warning. On MySQL command line you can issue a "SHOW WARNINGS" command that will give you warnings that were generated in your last SQL statement, however i'm not sure PHP has a function to grab this information.

 

As for using the to_days() function, I would normally stay away from using proprietory functions UNTIL you absolutely have to use them. Keep it SQL standard, that way should you ever change DBs you can use the same sql . CURRENT_TIMESTAMP should be the favoured usage.