selecting datetime values - between two dates

[gogglecollector]

hey there everyone. i’m writing a calendar script. what this calendar script will do is access the “calendar” database and do a search for all items with a datetime somewhere between now and 30 days from now. what kind of syntax would i use for this?

here’s what i’ve got so far:
[php]
$querystring = “SELECT name, datetime, description, contactname, contactphoneno, contactemail FROM calendar”;
[/php]

yup, don’t have anything else besides that. so here are my problems:

1. how do i use mysql to determine the datetimes for now and 30 days from now?
2. what kind of query would i write to select all items with datetimes between now and that datetime which is 30 days away?

oh, and i might mention this. i’m using a datetime whose length is 14 characters. so, in other words… the date format is going to be an integer like this: “20040727034415” (the time at which i’m writing this )

thanks for any help you can provide!

[Ragster00]

You will want to look into using BETWEEN in your mysql query.

And since you are using a datetime field type I think you could use strtotime() – http://www.php.net/strtotime

Also if the strtotime works out for you I think you will be able to easily add 30 days to your date.

[peg110]

i’m using a datetime whose length is 14 characters. so, in other words… the date format is going to be an integer like this: “20040727034415” (the time at which i’m writing this :D)

Well if you field is of type INTeger, then you are making things difficult for yourself. If you use a field type of INTeger, then consider using the date/timeformat of a UNIX Timestamp. (Number of elapsed seconds from the Unix Epoch on January 1, 1970). Otherwise use the the Field type of TIMESTAMP(14) and then as Ragster suggested utilize the BETWEEN syntax.

I would also suggest going to the nearest book store and get an SQL handbook. I use Sams Teach yoursefl SQL in 10 Minutes Which is just a quick reference guide. When I bought it (3+ years ago) it was $15.00 USD. Well worth the money. Otherwise get familiar with http://www.mysql.com/

Hope this helps

[gogglecollector]

well, i’m well familiar with mysql.com. and fortunately, i know enough to make the column type a datetime (instead of timestamp - would it be better to use timestamp?) i tried searching through mysql for information on BETWEEN because i thought i’d heard stuff about that, but i looked all around and it didn’t mention it wherever i looked. thanks for the 10 minute guide thing though, i’ll look into that. i have sams php in 24 hours, which is highly useful, and it might mention something in there as well.

[peg110]

Well I didn’t really take note of the differences in DATETIME and TIMESTAMP types, but you can see them at http://dev.mysql.com/doc/mysql/en/DATETIME.html however, I did notice that the date you presented is in TIMESTAMP(14) format http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html That’s why I made the reference.

Anyway, you can find info on searching the DATE ranges at

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html as well.

Hope it helps.

[lig]

BETWEEN
http://dev.mysql.com/doc/mysql/en/Compa … ators.html

[gogglecollector]

thanks