mysql ADN php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
c0mrade
Forum Newbie
Posts: 19
Joined: Mon Oct 06, 2008 4:17 am

mysql ADN php

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: mysql ADN php

Post 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...
c0mrade
Forum Newbie
Posts: 19
Joined: Mon Oct 06, 2008 4:17 am

Re: mysql ADN php

Post 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 ? :banghead:
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: mysql ADN php

Post 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.
c0mrade
Forum Newbie
Posts: 19
Joined: Mon Oct 06, 2008 4:17 am

Re: mysql ADN php

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: mysql ADN php

Post 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
Post Reply