MySQL PHP & Date converstions

[gemini85]

I currently have a form where the user puts in a persons date of birth in the format YYYY-MM-DD. They also add other information to the form, mainly from drop down menu's (some dynamic from the database). The date is added as a date to the database and the other info either as text or an int.

I am wondering the easiest way to convert these dates to DD-MM-YYYY (im from the UK) and work out their age based on the date/time when the page is loaded (probably using the servers clock). I think it would be easier converting the dates to the dd-mm-yyyy then adding them to the database but dont know how to do this.

I'm planning on adding search capabilities to a seperate page for ages in ranges (20-30 in 1 drop down box and 31-40 in another for example). These would then display the records off all the people within the specified age range and allow their individual pages to be loaded. I dont think I need help with the last part (ist just a simple query I think) but I've explained what I plan to do to make it easier to understand what I 'm wanting to achive with the dates.

I dont think its that complicated a thing to do but I've not really seen any clear documentation on it so some help would be greatly appreciated 🙂

Thanks in advance

[rachel2004]

<?php
$today = "2004-07-15";

$sql = "SELECT UNIX_TIMESTAMP(my_date_column)
        FROM my_table
        WHERE my_date_column = '$today'";

$result = mysql_query($sql, $db_connection);

$row = mysql_fetch_array($result);

$month = date('F', $row['UNIX_TIMESTAMP(my_date_column)']);
$date  = date('j', $row['UNIX_TIMESTAMP(my_date_column)']);
$year  = date('Y', $row['UNIX_TIMESTAMP(my_date_column)']);

echo "In Europe, today is: ".$month." ".$date.", ".$year;
?>

I've seen several ways of converting date columns from YYYY-MM-DD to anything you want.

If you do it in PHP, you have several options. If you plan on doing date comparisons, convert everything into a Unix timestamp and work with them like basic arithmetic. You'll see in my example that I used the MySQL function UNIX_TIMESTAMP to convert a date column to a Unix timestamp. See the MySQL manula for details.

If you wish to do your date comparisons in PHP, look into the function mktime() among others. Like I said, there are several ways of doing it and everyone has their favorite. I am only making one suggestion of a function to use.

Also, search through this forum as date comparison questions are frequently asked (and frequently answered).