Jump to content

deriving age from someone's birthday


webdeveloper123
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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)

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

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.