计算年龄 SQL 查询 - Calculating Age Sql query

美好的一天，伙计们，我这里有我的代码，可以从出生日期开始获取存储在我的数据库中的年龄。

$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>";
}

所有数据都是提取的，但唯一的问题是第一行的年龄与我的其余数据相同。我的代码有什么问题？有人帮忙，谢谢。

您正在从值数组中打印预取的年龄。
而是从当前行读取并用于显示。

更改 SQL 查询，如下所示：

$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";

更改：

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

至：

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

问题是您正在获取单个时间的年龄，在 while 循环中获取 dob，然后计算年龄并在 while 循环本身中打印年龄。

您正在获得年龄并将其用于其余数据。尝试这样的事情：

<?php
$connect = mysql_connect("localhost", "root", "");
mysql_select_db("mydb", $connect);
$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)) {
        $query1 = "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 where id = $row[id]";
        $result1 = mysql_query($query);
        $values1 = mysql_fetch_assoc($result);
        $age = $values1['age'];
        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>";
}

考虑到您的年龄公式是正确的，请尝试此查询。

// query will give all records and age for each record.
    $query="SELECT a.*, (SELECT TIMESTAMPDIFF(YEAR,dob,NOW()) FROM data AS b WHERE a.id = b.id) AS age FROM data AS a";
// rather than getting two result sets, you have age in the same record now, access it as a field.
    $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>".$row['age']."</td>"; // get age from db results
    echo "</tr>";
    }
    echo "</table>";
    }