Page 1 of 1

Dates of Birth and Current Age

Posted: Thu Jul 02, 2009 4:11 pm
by DAVID87
Hi all,

does anyone know how I could store a bate of birth (i.e. what format) in my MySQL database table and then on a page display the persons current age in years?

I have heard that date functions can be tricky so I thought I would ask for some advice or some helpful direction before I try to tackle this. I'd rather not get too stressed when I try and it just doesn't work over and over again.

If someone could help out or if they could point me in the direction of somewhere the would explain how a date can be stored properly and the formulas run on it I would be grateful.

Re: Dates of Birth and Current Age

Posted: Thu Jul 02, 2009 5:30 pm
by requinix
You can store a date as a DATE (YYYY-MM-DD) or as a Unix timestamp (which is a number).
For the former it's easy to display the date in that format, but you need a MySQL function or a couple PHP functions to reformat it into something else.
For the latter there's no "default" date format so you have to format it yourself using a couple MySQL functions or one of PHP's every time you want to display it.

Think about how you're going to use this date, how it will be displayed, how often, etc. then decide which style you want.

To calculate age take a look at this one thread that was posted a few days ago.

Re: Dates of Birth and Current Age

Posted: Fri Jul 03, 2009 6:18 am
by DAVID87
Thanks,

I know the logic that I need behind this but I am struggling abit with the code to get this type of function working.

can anyone offer any help?

Re: Dates of Birth and Current Age

Posted: Fri Jul 03, 2009 8:16 am
by Eric!
The code I posted in this thread should get you started.
viewtopic.php?f=1&t=102259

Do you need help with the mysql side too?

Re: Dates of Birth and Current Age

Posted: Fri Jul 03, 2009 9:02 am
by DAVID87
Any help would be appreciated.

Currently I have stored in my database for each user a number of fields that are all to be pulled back using this code

Code: Select all

 
<?php
 
include 'dbc.php';
 
$sql="SELECT * FROM membership_test";
$result = mysql_query($sql);
 
?>
 
    <table width="100%">
        <tr>
        <td class="rowQ" width="05%">id</td>
        <td class="rowQ" width="10%">Name</td>
        <td class="rowQ" width="20%">Home Number</td>
        <td class="rowQ" width="20%">Mobile Number</td>
        <td class="rowQ" width="20%">Email</td>
        <td class="rowQ" width="10%">DOB</td>
        <td class="rowQ" width="05%">Member</td>
        <td class="rowQ" width="10%">Age</td>        
        </tr>
       <?php
        while($rows=mysql_fetch_array($result)){
        if($color==1){
       echo "<tr class='rowA'>
         <td>$rows[id]</td>
         <td>$rows[full_name]</td>
         <td>$rows[home_number]</td>
         <td>$rows[mobile_number]</td>
         <td>$rows[user_email]</td>
         <td>$rows[DOB]</td>
         <td>$rows[member]</td>
         <td>&nbsp;</td>
         </tr>";
        $color="2";
        } else {
       echo "<tr class='rowB'>
         <td>$rows[id]</td>
         <td>$rows[full_name]</td>
         <td>$rows[home_number]</td>
         <td>$rows[mobile_number]</td>
         <td>$rows[user_email]</td>
         <td>$rows[DOB]</td>
         <td>$rows[member]</td>
         <td>&nbsp;</td>
         </tr>";
        $color="1";
        }   
        }
        mysql_close();
        ?>
</table>
 
I have done this so that the rows shown are alternating colours.

In the blank field I want to calculate the current age.

If you can help with any of this I would be greatful.

Thanks Eric! I have tried your code and this works fine however will only work out the current age of the first row of data. However when I try to include live data from my MySQL table it doesn't like this. It seems this will only work with a static date.

The DOB in the table are in a DATE format (yyyy-mm-dd)

Any help would be greatly appreciated.

Re: Dates of Birth and Current Age

Posted: Fri Jul 03, 2009 9:22 am
by Eric!
Please post your code. And it would help if you echoed some of your database data and post that too. The function will work as long as the input format is correct.