php - mysql数据检索中的嵌套while循环

Question

我正在使用嵌套的 while 循环来从 2 个不同的表(学生、年级，其中两个表都通过主键 - “id”链接)检索信息

我希望它以下列格式输出:

Student ID "123"

 - Grade 1 for ID "123"
 - Grade 2 for ID "123"

Student ID "555"

 - Grade 1 for ID "555"
 - Grade 2 for ID "555"

Student ID "666"

 - Grade 1 for ID "666"
 - Grade 2 for ID "666"

目前它的输出是这样的:

Student ID "123"

 - Grade 1 for ID "123" 
 - Grade 2 for ID "123"
 - Grade 1 for ID "555"
 - Grade 2 for ID "555" 
 - Grade 1 for ID "666" 
 - Grade 2 for ID "666"

Student ID "555"
Student ID "666"

这是PHP代码

<?php
include ("../../php/account.php");

$dbh = mysql_connect ( $hostname, $username, $password )
                   or die ( "Unable to connect to MySQL database" );
mysql_select_db( $project );

$s = mysql_query("SELECT * FROM newstudent");
$r = mysql_query("SELECT * FROM grades");  

while($rows=mysql_fetch_array($s))
    {
    echo "Student ID: ".$rows['id']."<br/>";
    echo "First Name: ".$rows['firstname']."<br/>";
    echo "Last Name: ".$rows['lastname']."<br/>";
    echo "Email: ".$rows['email']."<br/>";
    echo "<br/>";

            while($rows=mysql_fetch_array($r))
            {
                echo "Subject: ".$rows['subject']."<br/>";
                echo "Grade One: ".$rows['gradeone']."<br/>";
                echo "Grade Two: ".$rows['gradetwo']."<br/>";
                echo "Grade Three: ".$rows['gradethree']."<br/>";
                echo "<br/>";                
            }

    }

?>

如果有人知道解决方案，请帮助我!非常感谢!

Answer 1

不要使用嵌套循环，使用JOIN 在一次查询中获取所有结果:

$q = mysql_query("SELECT * FROM newstudent s
                  JOIN grades g ON s.id = g.student_id
                  ORDER BY s.id") or die (mysql_error());

$last_student = null;
while ($row = mysql_fetch_assoc($q)) {
    if ($row['id'] !== $last_student) {
        $last_student = $row['id'];
        echo "Student ID: ".$row['id']."<br/>";
        echo "First Name: ".$row['firstname']."<br/>";
        echo "Last Name: ".$row['lastname']."<br/>";
        echo "Email: ".$row['email']."<br/>";
        echo "<br/>";
    }
    echo "Subject: ".$row['subject']."<br/>";
    echo "Grade One: ".$row['gradeone']."<br/>";
    echo "Grade Two: ".$row['gradetwo']."<br/>";
    echo "Grade Three: ".$row['gradethree']."<br/>";
    echo "<br/>";                
}