How do i loop through but replace missing data with blanks in table

Question

I am trying to display a table to show all the subjects the first student takes, then all the progress grades the student has made in that subject.

However, a student may not have a grade in a certain column so i need to place a blank or 'no grade' in place of it. Instead i get them stacked side by side...

As you can see below '7(Pc3)' in English should be in the 'PC3' column and 'PC2' should say no grade or blank.... If possible - Thanks

I have the loop working to fetch the students, plus the loop working to fetch all the subjects for that student.

And can display all the grades - but they don't line up with the right column

while ($res = $result->fetch_assoc()) {
        echo "<tr><td>" . $res['subname'] . "</td>";

        $result2 = mysqli_query($mysqli, "SELECT *
                                            FROM grades
                                            JOIN gradesets ON grades.gradeset_id = gradesets.id
                                            WHERE grades.student_id = {$row['id']} 
                                            AND grades.subject_id = {$res['id']} 
                                            ORDER BY grades.gradeset_id ") or die($mysqli->error);

        while ($res2 = $result2->fetch_assoc()) {

            echo "<td>" . $res2['grade'] . "</td>";
            //echo "<td>" . $res2['gradeset_id'] . "</td>";
            //print_r($res2);
            $resset = $res2['gradeset'];
            $resset2 = substr($resset, -1);
            //print_r($resset);
            //print_r($resset2);

        }
    }

So i can echo out the right grades, but need to test they match up in the right columns... Here is the full code if needed...

$student = $mysqli->query("SELECT * FROM student");

echo "<center>";
echo "<h2>Data Wall</h2>";
echo "<h3>PHP</h3>";
echo "<hr/>";

while ($row = $student->fetch_assoc()) {
    echo "<table border='1'>
            <tr>
                <th>ID</th>
                <th>STUDENT</th>
                <th>HOUSE</th>
            </tr><br>";
    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";
    echo "<td>" . $row['stuname'] . "</td>";
    echo "<td>" . $row['house'] . "</td>";
    echo "</tr><br><tr></tr><tr></tr><tr></tr><tr></tr><tr></tr><tr></tr><tr></tr>";
    echo "<tr><th>SUBJECTS</th><th>PC1</th><th>PC2</th><th>PC3</th><th>PC4</th></tr>";
    $result = mysqli_query($mysqli, "SELECT  subjects.id,subjects.subname
                                    FROM student
                                    JOIN grades ON student.id = grades.student_id
                                    JOIN subjects ON subjects.id = grades.subject_id
                                    WHERE student.id = {$row['id']} 
                                    GROUP BY subjects.subname ORDER BY subjects.id ") or die($mysqli->error);

    while ($res = $result->fetch_assoc()) {
        echo "<tr><td>" . $res['subname'] . "</td>";

        $result2 = mysqli_query($mysqli, "SELECT *
                                            FROM grades
                                            JOIN gradesets ON grades.gradeset_id = gradesets.id
                                            WHERE grades.student_id = {$row['id']} 
                                            AND grades.subject_id = {$res['id']} 
                                            ORDER BY grades.gradeset_id ") or die($mysqli->error);

        while ($res2 = $result2->fetch_assoc()) {

            echo "<td>" . $res2['grade'] . "</td>";
            //echo "<td>" . $res2['gradeset_id'] . "</td>";
            //print_r($res2);
            $resset = $res2['gradeset'];
            $resset2 = substr($resset, -1);
            //print_r($resset);
            //print_r($resset2);

        }
    }
}
echo "</tr>";
echo "</table>";
echo "</center>";
$mysqli->close();
?>

score 0 · Answer 1 · answered Jun 25 '19 at 14:55

Since PHP 5.3 you can use Elvis operator - ?:

And since PHP 7 you are able to use Null Coalescing Operator - ??

Either of these you can use to display some other information if you row is empty. For example (PHP 7+):

echo "<td>" . ($res2['grade'] ?? 'No grade') . "</td>";

Would result to either a grade, or No grade text if string is empty or false.

Hope this helps!

score 0 · Answer 2 · answered Jun 25 '19 at 14:56

In your inner query, you're doing an INNER JOIN, which selects only those rows that have a match in the gradeset table. It looks like you want a LEFT OUTER JOIN, so that you get null placeholders where there is no match:

SELECT *
FROM grades
LEFT JOIN gradesets ON grades.gradeset_id = gradesets.id
WHERE grades.student_id = {$row['id']} 
AND grades.subject_id = {$res['id']} 
ORDER BY grades.gradeset_id

This way, in your query result, instead of getting:

4 (PC1)
7 (PC3)
6 (PC4)

You'll get:

4 (PC1)
null
7 (PC3)
6 (PC4)

score 0 · Answer 3 · answered Jun 25 '19 at 15:03

You could build an array of empty grades and then replace them with any data from the query. Like so:


$grades = [1 => '', 2 => '', 3 => '', 4 => ''];

while ($res2 = $result2->fetch_assoc()) {
    $grades[$res2['gradeset']] = $res2['grade'];
}

foreach ($grades as $grade) {
    echo "<td>" . $grade . "</td>";
}

How do i loop through but replace missing data with blanks in table

3 Answers3