webdeveloper123 Posted April 22, 2022 Share Posted April 22, 2022 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted April 22, 2022 Share Posted April 22, 2022 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. Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 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. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted April 22, 2022 Share Posted April 22, 2022 How about showing us you best php attempt at this problem? Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 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. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted April 22, 2022 Share Posted April 22, 2022 What part do you consider to be your attempt? Just show us that here. Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 btw, I did a var_dump($birthday) and I only got this: string(10) "1988-08-19" Why is there only ever one value? Quote Link to comment Share on other sites More sharing options...
ginerjm Posted April 22, 2022 Share Posted April 22, 2022 Show me The Code That is Doing the Calculation, Please. Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 foreach ($table as $value){ $date1=date_create("now"); $date2=date_create($birthday); $diff=date_diff($date1,$date2); } Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted April 22, 2022 Share Posted April 22, 2022 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 22, 2022 Share Posted April 22, 2022 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) Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 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 Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 maybe I should have mentioned that earlier, lol Quote Link to comment Share on other sites More sharing options...
Barand Posted April 22, 2022 Share Posted April 22, 2022 $result = $con->query("select fname , timestampdiff(year, dob, curdate()) as age from employee "); foreach ($result as $row) { echo "{$row['fname']} {$row['age']} <br>"; } giving Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 (edited) 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted April 22, 2022 Share Posted April 22, 2022 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??? Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 (edited) 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 Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 That's why I'm looking into doing Zend courses, as it is a much better level of teaching Quote Link to comment Share on other sites More sharing options...
Barand Posted April 22, 2022 Share Posted April 22, 2022 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? Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted April 22, 2022 Share Posted April 22, 2022 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. Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 22, 2022 Share Posted April 22, 2022 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. Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted April 22, 2022 Author Share Posted April 22, 2022 Yes, thanks barand Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.