[thelist] date selection and mysql and PHP

[Getafixx]

Hi Burhan and Jay,

I tried both methods you suggested and both didn't work..

At first I thought that this would be perfect and a nice simple solution 
but this doesn't work, or returns no matches

SELECT * FROM `my_table` WHERE `date_time` LIKE '20050509$'

then Burhan suggested this, but again this fails to produce any rows.
SELECT * FROM `my_table` WHERE DATE_FORMAT('%Y%m%d',`date_time`) = 
'20050509';

The original method I think will be used and the coding to get to the 
SELECT * FROM `my_table` WHERE `date_time` BETWEEN "20050509" AND 
"20050510" ORDER BY `date_time`

it's not clean, but at least I get rows returned.

Again thanks for everyone's help.

Justin



Burhan Khalid wrote:
> 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.
> 

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

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