[thelist] date selection and mysql and PHP

[Burhan Khalid]

Getafixx wrote:
> OK I am working on a simple calender (OK I know it's pretty dull stuff)
> 
> My questions are - I am storing the date and the timing of an event with 
> DATETIME, which seams to work OK.
> 
> 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"

I can't believe this query works on a DATETIME type field, because the 
format of that field is '0000-00-00 00:00:00'

You can check the different date and time types in the manual to see 
their default format by checking the "zero" value.

[ snipped ]

> Maybe my question is why can't I just say where date row = <my date> 
> where I try and match just the first part of the date with the first 
> part of any dates in the table
> 
> SELECT * FROM `my_table` WHERE `date_time` = "20050509"
> 
> but this doesnt seam to work...

SELECT `col`, `col2` FROM `my_table` WHERE 
DATE_FORMAT('%Y%m%d',`date_time`) = '20050509';

> 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 not a database guru -- but I believe that many small select queries, 
not one large complicated one are generally faster. The speed depends on 
many other factors, such as your table size, the type of query, indices 
on the table, etc.

In your case, I don't think it will matter that much.