[thelist] date selection and mysql and PHP

[Getafixx]

Hi Scott.

This seams to work, so thank you very much.

I can safely say that I have been looking at as many references as 
possible before I post a question here, but thank you for passing that 
one on, I had been to it all day yesterday.

I will do some speed tests but this little app at the moment is just for 
me, so speed isn't necessarily needed, but I don't like writing bad 
(slow) code.

Thanks

Justin

Scott Wehrenberg wrote:
> 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.

-- 
==============================================================
Justin / Getafixx                                07967 638 529
mailto:qwerty1 at getafixx.com

http://getafixx.com
http://getafixxhosting.com for really cheap web hosting
==============================================================