[thelist] date selection and mysql and PHP
Getafixx
getafixx at getafixx.com
Tue May 10 12:31:10 CDT 2005
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
==============================================================
More information about the thelist
mailing list