Jump to content

Calculate age from data field in database


zazu
Go to solution Solved by Barand,

Recommended Posts

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!

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • Solution

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 by Barand
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.