时间戳

首页 > 解决方案 > 数据表未显示来自查询的所有数据

php - 数据表未显示来自查询的所有数据

问题描述

我有一个产生正确结果的查询，之后我可以显示记录但不是我想要的格式。工作格式是学生姓名，年级。我只想连续显示一次学生姓名，然后显示每个年级。我的代码在显示表格时会丢失数据。

第一行丢失 0 个条目。第 2 行丢失 1 个条目。第 3 行丢失 2 个条目。这种模式一直持续到查询没有更多数据要获取。我有以下代码。

$sql = "SELECT CONCAT(s.last_name, ', ',s.first_name) AS 'Student Name',g.grade 
        FROM `grades` g 
            JOIN assignments a ON a.assignment_id=g.assignment_id 
            JOIN teacher t ON t.teacher_id=a.teacher_id 
            JOIN students s ON s.student_id=g.student_id 
        WHERE a.teacher_id='" . $_SESSION['userId'] . "' 
        AND a.subject_id='2' 
        ORDER BY s.last_name ASC";

$sqlCount = "SELECT COUNT(assignment_name) 
             FROM `assignments` 
             WHERE subject_id = '2' 
             AND teacher_id= '" . $_SESSION['userId'] . "';";

//gets the number of assignments so it can display the table
$resultCount = mysqli_query($connection, $sqlCount);
$countNum = mysqli_fetch_assoc($resultCount);
$counter = $countNum['COUNT(assignment_name)'];
$result = mysqli_query($connection, $sql); 

while($row2 = mysqli_fetch_assoc($result)){
    if($counter == 0){
        echo"<tr><td>{$row2['Student Name']}</td>";
        echo"<td>{$row2['grade']}</td>";
        $counter++;
    } else if($counter < $countNum['COUNT(assignment_name)']){
        echo"<td>{$row2['grade']}</td>";
        $counter++;
    } else{ 
        $counter = 0;
        echo"</tr>";
    }
}

这是我想要的最终输出格式。由于我的帐户是新帐户，我不得不将其设为链接

在此处输入图像描述

标签： phpmysqlhtml-table

解决方案

这似乎要简单得多。

$sql = "SELECT CONCAT(s.last_name, ', ',s.first_name) AS StudentName,g.grade 
        FROM `grades` g 
            JOIN assignments a ON a.assignment_id=g.assignment_id 
            JOIN teacher t ON t.teacher_id=a.teacher_id 
            JOIN students s ON s.student_id=g.student_id 
        WHERE a.teacher_id='" . $_SESSION['userId'] . "' 
        AND a.subject_id='2' 
        ORDER BY s.last_name,s.first_name ASC";


$result = mysqli_query($connection, $sql); 

$last_stud = null;

while($row2 = mysqli_fetch_assoc($result)){
    if($last_stud != $row2['StudentName']){

        // close previous <tr>
        if ( $last_stud !== null ) {
            echo '</tr>';
        }

        $last_stud = $row2['StudentName'];
        echo"<tr><td>{$row2['StudentName']}</td>";
        echo"<td>{$row2['grade']}</td>";
    } else {
        echo"<td>{$row2['grade']}</td>";
    }
}
echo '</tr>';

推荐阅读