PHP/mysql - WHERE date is smaller than...

[daydreamer]

I need to select a bunch of rows that are either before or after given dates.

 

The way I was going to do it is to Select *, and then a while loop which checks the dates are bigger or smaller than the given date.

 

Is it possible to do it directly from the mysql statement.

 

eg:

"SELECT * FROM table WHERE date>'$givenfromdate' AND date<'$giventodate'"

 

The dates stored in the mysql table are in this format:

2008-12-26 17:32:34

 

So how could I do this? In the while loop I would just convert both dates to the mktime format and compare to see which is biggest - is it possible to also convert the dates to the mktime format in the mysql statement?

 

Thanks.

[Mchl]

Are dates stored as DATETIME datatype, or as strings?

[daydreamer]

mysql_query("SELECT COUNT(*) FROM table WHERE UNIX_TIMESTAMP(date)>$from AND UNIX_TIMESTAMP(date)<$to");

 

Ok I think this works good havent fully tested it.

 

they are datetimes.

[Mchl]

they are datetimes.

 

Good for that. This means (as you have already noticed) that you can use mysql's date time functions

 

How about

SELECT COUNT(*) FROM table WHERE UNIX_TIMESTAMP(date) BETWEEN $from AND $to

[revraz]

You can do a WHERE with BETWEEN if you want.

[daydreamer]

ok cool, which is the best way?

[revraz]

The best way is the way that works for you.

 

ok cool, which is the best way?