i have a table in database that contains birthday column varchar()- i choose varchar
because ecoder could input birthday like
1. 1993-14-03
2. 1993-03-14
3. March 14
4. March 14,1993

Now my problem i want to detect what month is their birthday could some help me?

4 Years
Discussion Span
Last Post by code739

can you post your code for taking birthday data from user
if you are using select box then you can give month name in it and store month by name


no,...........such data above are already stored in database
the problem is determining the month of every data as i retrive it to php

like if i have this code

$sql = "select birthday from person";
$query = mysql_query($sql);
$result = mysql_fetch_array($query);
$birthday = $result['birthday']; this could have the value i posted above..

i need some creative idea or code perhaps =) thanks

Edited by code739


i know above data already in data base but i m asking how you insert that data in data base store months by name in database then you easily determine what month user selected


There are some ways to extract the information if your date can be recognized as a specific one. The problem remains with a date like 2013-01-02. There is no way to tell if the month is January or February.


be impossible to get 100% of the data back, 2012-01-04 and 2012-04-01 both valid dates and no idea which format it was entered as, you could get the obvious ones though, i'd use a combo of substr() and locate()

SELECT SUBSTR('2012-22-07',LOCATE('-',CURDATE())+1,2) AS `second_num`;
SELECT SUBSTR('2012-22-07',LOCATE('-',CURDATE())+4,2) AS `third_num`;

then you can find out the ones above 12 must be the day so the other is the month - the rest are 50 50 chance.

for the worded dates like 'March' i'd use php's strtotime function cause that's pretty good at translating string dates

foreach($data as $row){
    echo $row['date'].' -> '.date("Y-m-d",strtotime($row['date']))."<br/>\r\n";

Edited by Biiim: added line break


Your problem is the data format for the DB. You should only accept ONE format, typically unix date: Y-m-d.
When an user presents their details, your form design should allow them to submit data in a consistent format, such as from a datepicker or multiple dropdowns. I would recommend the former, as multiple dropdowns can give you false dates, e.g. 1967-02-31, and checking each dropdown in turn is a royal PITA.
However, if you've got yourself into this mess and can't change your DB/form, then use strtotime() and convert to the format of your choice.

Anyway you can search in MySQL: MONTHNAME() = 'February' on certain formats, and LIKE '%February%' for others.
This type of date: 1993-14-03 is nonsense, and I can't see how you'd determine this. It goes back to what I stated earlier - DO NOT allow your users to enter their own formats.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.