deriving age from someone's birthday

[webdeveloper123]

Hello,

I am trying to derive someone's age from their birthdate (which is stored in db) and print out age in years for every single record on a "View" page which lists all records from the database. Thing is it will only correctly calculate the age of the first record in the table and print out the same age for every single record. I've tried multiple ways including SQL statement using TIMESTAMPDIFF, but it only prints out the age of person for the first record. Here is some code. This is as close as I have got:

<?php

$query = "SELECT 
 Form.FormId, 
 Form.FirstName, 
 Form.LastName, 
 Form.Email, 
 Form.Age, 
 Form.Birthdate, 
 Form.FavLanguage, 
 GROUP_CONCAT(Vehicle.VehSelection SEPARATOR ', ') AS VehSelection
FROM 
  Vehicle 
  RIGHT JOIN Form 
    ON Form.FormId = Vehicle.FormId
GROUP BY 
  Form.FormId";
  
$result = mysqli_query($link, $query);


	if (!$result) {
		printf("Error in connection: %s\n", mysqli_error($link));
		exit();
	}

  $table = [];
	while ($row = mysqli_fetch_assoc($result)) {
		$table[] = $row;

	}

		if ( count($table) == 0) {
 //echo ("No records found");
 exit;

}
else
{



  $birthday = $table[0]["Birthdate"];
}

foreach ($table as $value){


$date1=date_create("now");
$date2=date_create($birthday);
$diff=date_diff($date1,$date2);
}



?>

<td><?php echo $diff->format("%y years"); ?></td>

The SQL on the above code was meant for something else, but I thought seeing as I'm already pulling the birthdate from a SQL statement, on the same page, I thought i'd reuse it.

Thanks

[ginerjm]

So you aren't showing us any attempt at determining someone's age even though you have a column named 'age' in this table.  (IMHO, a bad db design.)  What have you done to research this problem on your own so far.

There is a pretty good selection of date functions in PHP that I'm sure would reveal a solution to you should you take a gander at the official PHP function reference.

Suggestions for this posted code:

1  Why go thru the loop of your query results before checking to see if you had any results at all.  Checking the size of the table var you are assigning the results to is a bit late, don't you think?

2 If you go thru your code with a closer eye you would see that you are a) using a single birthday value prior to enter the loop to evaluate them all and b) assigning today's date multiple times while in this same loop instead of swapping those 2 operations places.

[webdeveloper123]

I have tried it several different ways, as I said in my post I used SQL and 3-4 other pieces of code but it didn't work properly. I even tried the above code without a foreach and I got the same result. Yes It was bad design but i'm fixing it now. How can it be a single value(birthday) when It comes from the database of almost 200 records.

[ginerjm]

How about showing us you best php attempt at this problem? 

[webdeveloper123]

As I said in my post, that's as close as I have got. I've been on it for 2-3 days now and wanted some help.

[ginerjm]

What part do you consider to be your attempt?  Just show us that here.

[webdeveloper123]

btw, I did a var_dump($birthday) and I only got this:

string(10) "1988-08-19"

Why is there only ever one value?

[ginerjm]

Show me The Code That is Doing the Calculation, Please.  

[webdeveloper123]

foreach ($table as $value){


$date1=date_create("now");
$date2=date_create($birthday);
$diff=date_diff($date1,$date2);
}

 

[webdeveloper123]

I tried this as well:

foreach ($table as $value){


$date1=date_create("now");
$date2=date_create(($value["Birthdate"]));

$diff=date_diff($date1,$date2);
}

And I tried both of them without the loop as well

[ginerjm]

Ok  I'm tired of trying to get you to communicate with me.  Here is a very simple solution.  Try and understand what is happening here.

$q = "SELECT First_name, Last_Name, DATE_FORMAT(Birth_date,'%m/%d/%Y') as dob
		FROM MMS_Members WHERE 1 limit 10";
$results = $pdo->query($q);

$today = date_create(date("m/d/Y"));  // only creae $today ONCE.

while($row = $results->fetch(PDO::FETCH_ASSOC))	// Do the loop using the actual query results instead of a manufactured $table var.
{
	$diff = date_diff(date_create($row['dob']), $today);
	echo "{$row['First_name)} {$row['Last_Name']} birthday is {$row['dob']} and age now is " . $diff->format('%y') ."<br>";
}

 

Edited April 22, 2022 by ginerjm

[Barand]

mysql> select * from employee;
+-------+-------+---------+------------+
| empid | fname | lname   | dob        |
+-------+-------+---------+------------+
|     1 | Peter | Smith   | 1985-01-26 |
|     2 | Paul  | Hartley | 1973-12-02 |
|     3 | Mary  | Baker   | 1980-04-11 |
|     4 | Jane  | Doe     | 1970-11-28 |
+-------+-------+---------+------------+
4 rows in set (0.00 sec)

mysql> select fname
    ->      , timestampdiff(year, dob, curdate()) as age
    -> from employee;
+-------+------+
| fname | age  |
+-------+------+
| Peter |   37 |
| Paul  |   48 |
| Mary  |   42 |
| Jane  |   51 |
+-------+------+
4 rows in set (0.00 sec)

 

[webdeveloper123]

Thanks to both of your replies. That's what I did Barand, I used:

SELECT TIMESTAMPDIFF (YEAR, Birthdate, CURDATE()) AS AGE FROM Form;

It gave me correct results for all the records, but when I tried to put it into my code I did this:

SELECT TIMESTAMPDIFF (YEAR, Birthdate, CURDATE()) AS Age1 FROM Form WHERE Form.FormId = '$FormId'";

But when I tried to loop round it and output the records I only ever got result for first record. I'll try out solutions from above. Thanks

[webdeveloper123]

maybe I should have mentioned that earlier, lol

[Barand]

$result = $con->query("select fname
                            , timestampdiff(year, dob, curdate()) as age
                       from employee
                      ");
foreach ($result as $row) {
    echo "{$row['fname']} {$row['age']} <br>";
}                                                                                                                      

giving

[webdeveloper123]

ahh you guys are looping around the result set and not the $table array, I spotted that in ginerjm commented code. Is that a better way of doing it?

Edited April 22, 2022 by webdeveloper123

[ginerjm]

ABSOLUTELY!!!  Why do the loop of the results just to create a needless $table when you have what you need right in front of you with the query results???

[webdeveloper123]

Tbh, the book I bought to learn php was really bad. That's why my code is not that good, as when I learnt it, it was a very bad book. 

Edited April 22, 2022 by webdeveloper123

[webdeveloper123]

That's why I'm looking into doing Zend courses, as it is a much better level of teaching

[Barand]

16 minutes ago, webdeveloper123 said:

It gave me correct results for all the records, but when I tried to put it into my code I did this:

SELECT TIMESTAMPDIFF (YEAR, Birthdate, CURDATE()) AS Age1 FROM Form WHERE Form.FormId = '$FormId'";

But when I tried to loop round it and output the records I only ever got result for first record.

When you do a query to select only 1 record, why would you expect output for more than one record?

[webdeveloper123]

Just now, Barand said:

When you do a query to select only 1 record, why would you expect output for more than one record?

I didn't know it was only querying one record, I thought all the FormId's from the database were stored in there,  as I've done similar sql code like that before and it worked

[ginerjm]

Yes all of the formids were probably stored in there but you wrote a query with a WHERE clause asking for one record that matched whatever was in the variable named $FormId.

PS  Suggestion.  Do not use upper and lowercases in your variable names.  There is no reason to and it can cause you problems down the road when you forget how you spelled a variable and your code is not working correctly because you spelled something in two ways.  All lower case makes perfect sense for vars.  Maybe you could use caps in table column names but not your vars.  PHP is a case sensitive language so don't introduce cases in your code.

[webdeveloper123]

1 minute ago, ginerjm said:

Yes all of the formids were probably stored in there but you wrote a query with a WHERE clause asking for one record that matched whatever was in the variable named $FormId.

I just thought it would take each FormId, 1 by 1, and calculate the age for each record and print it out

I understand what your saying about variables.

[Barand]

6 minutes ago, webdeveloper123 said:

I just thought it would take each FormId, 1 by 1, and calculate the age for each record and print it out

A query returns those rows for which the WHERE clause evaluates to TRUE. For example

mysql> select fname
    ->      , timestampdiff(year, dob, curdate()) as age
    -> from employee
    -> where fname = 'Peter';
+-------+------+
| fname | age  |
+-------+------+
| Peter |   37 |
+-------+------+
1 row in set (0.00 sec)

mysql> select fname
    ->      , timestampdiff(year, dob, curdate()) as age
    -> from employee
    -> where timestampdiff(year, dob, curdate()) > 45;
+-------+------+
| fname | age  |
+-------+------+
| Paul  |   48 |
| Jane  |   51 |
+-------+------+
2 rows in set (0.01 sec)

If there is no WHERE clause, all rows are returned.

[webdeveloper123]

Yes, thanks barand