Calculate age from data field in database

[zazu]

Hi guys,

 

I'me trying to get the age of a user that i have in my database based on the data type field in database.

So in my database in tha data field i have 20.02.1989

Now i want to display the age of the user with a specific ID, i must mention that i want to display the age of all users that will be register in the database. 

 

How can i do that because i've tried a lot of things and not of them seems to be working.

My code is below:

$dateValue = "SELECT EXTRACT(YEAR FROM data_nasterii) FROM bonacuora_clients WHERE id='$id'";
$c=date('Y');
$varsta = $c-$dateValue;

Thank you!

[Psycho]

You should first fix the database to change that field to a date type. The database has lots of useful functions and processes for working with dates, but you can't use them on data that is not a date type. Otherwise, your best option is to read the data into PHP, parse it into a date format and then do the calculation.

 

EDIT: My response above is based on what I know date types to by in MySQL and MS SQL. You may be using a different database that stores dates differently.

Edited May 6, 2015 by Psycho

[zazu]

It is a date type  

[Psycho]

It is a date type

 

Well, you did state data type. What type of database are you using? MySQL and MS SQL do not store dates in the format 20.02.1989, they would use 1989-02-20

[zazu]

I use mysql and the date type is 1989-06-03

[Barand]

EG

$dob = DateTime::createFromFormat('d.m.Y', '20.02.1989');
$now = new DateTime();
$age = $dob->diff($now)->y;
echo $age;  // 26

But, as Psycho said, you really should fix your stored date format. You can't even sort by date with your format.

[zazu]

EG

$dob = DateTime::createFromFormat('d.m.Y', '20.02.1989');
$now = new DateTime();
$age = $dob->diff($now)->y;
echo $age;  // 26

But, as Psycho said, you really should fix your stored date format. You can't even sort by date with your format.

Understand, but i want to get the birthday values from database not to specifiy them manually.

[Psycho]

Here's a solution that I found via Google. 'dob' represents the Date Of Birth

 

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
 
FROM table_name

[zazu]

And how the final code should look like?

[Barand]

Assuming you now have your dates of birth (dob) as type date (yyyy-mm-dd)

<?php
$mysqli = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

//---------------------------------------------
// Method 1
// Calculate age using PHP
//---------------------------------------------
    function age($dob)
    {
        $dt = new DateTime($dob);
        return $dt->diff(new DateTime())->y;
    }

    $sql = "SELECT name
            , dob 
            FROM bonacuora_clients
            ORDER BY name";
    $result = $mysqli->query($sql);
    while ($row = $result->fetch_assoc()) {
        echo $row['name'] . " - " . age($row['dob']) . '<br/>';
    }
    
    
//---------------------------------------------
// Method 2
// Calculate age using SQL
//---------------------------------------------
    $sql = "SELECT name
            , DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d'))
              AS age 
            FROM bonacuora_clients
            ORDER BY name";
    $result = $mysqli->query($sql);
    while ($row = $result->fetch_assoc()) {
        echo $row['name'] . " - " . $row['age'] . '<br/>';
    }
?>

Edited May 7, 2015 by Barand

[zazu]

 

Assuming you now have your dates of birth (dob) as type date (yyyy-mm-dd)

<?php
$mysqli = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

//---------------------------------------------
// Method 1
// Calculate age using PHP
//---------------------------------------------
    function age($dob)
    {
        $dt = new DateTime($dob);
        return $dt->diff(new DateTime())->y;
    }

    $sql = "SELECT name
            , dob 
            FROM bonacuora_clients
            ORDER BY name";
    $result = $mysqli->query($sql);
    while ($row = $result->fetch_assoc()) {
        echo $row['name'] . " - " . age($row['dob']) . '<br/>';
    }
    
    
//---------------------------------------------
// Method 2
// Calculate age using SQL
//---------------------------------------------
    $sql = "SELECT name
            , DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d'))
              AS age 
            FROM bonacuora_clients
            ORDER BY name";
    $result = $mysqli->query($sql);
    while ($row = $result->fetch_assoc()) {
        echo $row['name'] . " - " . $row['age'] . '<br/>';
    }
?>

Hei Barad, thank you for your reply. The code that you provide in returns me 0 value insted of the age of the user.

[Barand]

The table

CREATE TABLE `date_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dob` date NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

The data

mysql> SELECT * FROM date_sample;
+----+------------+-------+
| id | dob        | name  |
+----+------------+-------+
|  1 | 1945-09-11 | Peter |
|  2 | 1949-01-22 | Fred  |
+----+------------+-------+

My output from the above code

METHOD 1
Fred - 66
Peter - 69

METHOD 2
Fred - 66
Peter - 69

[zazu]

 

The table

CREATE TABLE `date_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dob` date NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

The data

mysql> SELECT * FROM date_sample;
+----+------------+-------+
| id | dob        | name  |
+----+------------+-------+
|  1 | 1945-09-11 | Peter |
|  2 | 1949-01-22 | Fred  |
+----+------------+-------+

My output from the above code

METHOD 1
Fred - 66
Peter - 69

METHOD 2
Fred - 66
Peter - 69

Now worked perfectly! It was my bad! Thank you very much.