[thelist] date selection and mysql and PHP

Scott Wehrenberg swehren at gmail.com
Tue May 10 11:26:13 CDT 2005


On 5/9/05, Getafixx <getafixx at getafixx.com> wrote:
> I have been trying to craft a search to get ONLY the events on the day
> in question.. The only way I could do that and it was a big hack was to
> use this query, where I have to arse around with figuring out what the
> next day is and go from the correct day to the next day...
> 
> SELECT * FROM `events` WHERE `date_time` BETWEEN "20050509" AND
> "20050510" ORDER BY "20050509"

You may want to take a look at the section of the MySQL manual dealing
with date and tiem functions:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Here's how I would do your query:

SELECT * FROM `events` WHERE date < DATE_ADD(' 2005-05-09', INTERVAL 1
DAY) AND date > '2005-05-09' ORDER BY date

In my tests the query didn't work without the dashes. You could
probably add them using STR_TO_DATE() in the SQL if you didn't want to
bother handling it in PHP. Also you probably meant to order by date as
I'm pretty sure ORDER BY "20050509" won't change the order of
anything.

> Also on reading the db for a months worth of dates, would it be better
> to do one query for the whole month and then parse it up, or lots of
> small queries, for each day?

I'm no DB expert, but I'm pretty sure it's usually better to use one
query than a ton of small ones. It's relatively simple to do some
benchmarking though to find out which is better in your particular
instance.


More information about the thelist mailing list