计算Age Sql查询

Question

good day guys i have my code here to get age from date of birth stored in my database.

$connect=mysql_connect("localhost","root","");
mysql_select_db("mydb",$connect);

$query = "select date_format(now(), '%Y') - date_format(dob, '%Y') - (date_format(now(), '00-%m-%d') < date_format(dob, '00-%m-%d'))
as age from data";
$result = mysql_query($query); 
$values = mysql_fetch_assoc($result); 
$age = $values['age']; 

$query="select * from data";
$result=mysql_query($query);
if(mysql_num_rows($result)>0){
echo "<table align='center' border='1'>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>Last Name</th>";
echo "<th>First Name</th>";
echo "<th>Age</th>";
echo "</tr>";
while($row=mysql_fetch_array($result)){
echo "<tr>";
echo "<td>".$row['id']."</td>";
echo "<td>".$row['lname']."</td>";  
echo "<td>".$row['fname']."</td>";  
echo "<td>".$age."</td>";

echo "</tr>";
}
echo "</table>";
}

all data is fetch but the only problem is the age of the first row is the same as the rest of my data. what wrong with my code? anybody help please thank you.

Answer 1

You are printing pre-fetched age from values array.
Instead read from current row and use to display.

Change your SQL query as below:

$query = "select id, lname, fname, 
              date_format(now(), '%Y') - date_format(dob, '%Y') 
            - (   date_format(now(), '00-%m-%d') 
                < date_format(dob, '00-%m-%d'))
            as age
          from data";

Change:

echo "<td>".$age."</td>";

To:

echo "<td>".$row['age']."</td>";