Page 1 of 1
mysql ADN php
Posted: Mon Oct 06, 2008 4:32 am
by c0mrade
Hi all,
I'm not sure where to post exactly but I think this category fits the most. I'm trying to build a MySQL query or something that will do what I need, here is the problem :
MySQL table celebrities:
Name Surname DOB
Frank Sinatra 01-01-1919
Chuck Norris 10-03-1940
Tom Clancy 13-03-1940
Bruce Lee 21-03-1940
Now here is what I try .. to list or extract all celebrities born after 10-03-1940 ... or all celebrities born on 10-03-1940 and a week(7 days) after. *All DOB s except Chucks are fictional
Thank you for your replies in advance
Re: mysql ADN php
Posted: Mon Oct 06, 2008 4:44 am
by onion2k
c0mrade wrote:Now here is what I try .. to list or extract all celebrities born after 10-03-1940 ... or all celebrities born on 10-03-1940 and a week(7 days) after.
To clarify then, you want
two queries, one that lists everyone born on or after a certain date, and another that lists everyone born within 1 week of a certain date?
Everyone after 10-03-1940
Code: Select all
SELECT * FROM `celebrities` WHERE `dob` >= DATE('1940-03-10');
Everyone within a week of 10-03-1940
Code: Select all
SELECT * FROM `celebrities` WHERE `dob` BETWEEN DATE('1940-03-10') AND DATE_ADD(DATE('1940-03-10'), INTERVAL 1 WEEK);
I've not tested either of those...
Re: mysql ADN php
Posted: Mon Oct 06, 2008 3:44 pm
by c0mrade
thank you for your reply
is it possible to insert date into mysql in this format "10-03-1940" 'm-d-Y' ? and how .. when I enter values manually trough phpmyadmin after query is executed I get all zeros inside date field .. any hints ?

Re: mysql ADN php
Posted: Tue Oct 07, 2008 8:36 am
by onion2k
Assuming the column is a date or datetime type then the date must be entered in a non-ambiguous format. That is usually YYYY-MM-DD ... otherwise there's no way for MySQL to know if you mean DD-MM-YYYY (European format) or MM-DD-YYYY (American format).
You can enter it using the DATE() function.. eg DATE('MM-DD-YYYY') ... but what the date will be in the record depends on MySQL's settings. It's a bit dangerous doing that because you can end up with unexpected results if you're entering the date as American format and MySQL is set up for European. Using YYYY-MM-DD will always work regardless.
Re: mysql ADN php
Posted: Tue Oct 07, 2008 3:05 pm
by c0mrade
Thank you for your brief explanation. I'm now inserting records in American format and sql works fine. But when I'm displaying results I make them in European format by using explode() to make it in reverse order. Thank you again
Re: mysql ADN php
Posted: Tue Oct 07, 2008 3:07 pm
by onion2k
If you want to get dates out of a database table in a particular format that isn't the default it's best to use MySQL's DATE_FORMAT() function.. eg
Code: Select all
SELECT DATE_FORMAT("%d/%m/%Y", `date_column`) AS formatted_date FROM `table`
http://dev.mysql.com/doc/refman/5.0/en/ ... ate-format