用于具有动态行和列的html表的PHP，其中行数据依赖于列标题

Question

I have created an assessment system that grades each pupil on each lesson. I have a form that creates a table with the following headings:

• Pupil name
• Pupil ID
• Lesson title
• Lesson ID
• Lesson grade

To view the data I am trying to create a table that works a bit like a spreadsheet.
Where each row takes the pupil ID and searches in the database for the grade i.e.

SELECT * FROM table WHERE pupilid = "rowid" AND lessonid = "columnheading"

I can create the column and row headings using PHP with while loops, but I can't figure out how to make each cell link between the column heading and pupil ID.

The only way I have managed to create this is with floating divs.
It works but it is very hard to style and the names and grades are in different grids so it is difficult to sort.
I would really appreciate any help / links to ways to do this.
Each week new pupils could be added and each lesson column will be added to the spreadsheet.

The table should sort of look like the one below:

+-------+----+----+--- +
| Pupil | L1 | L2 | L3 |
+-------+----+----+----+
| John  | B  | C+ | D  |
+-------+----+----+----+
| Sarah | B  | A  | F  |
+-------+----+----+----+
|  Jim  | D  | A  | B  |
+-------+----+----+----+

I can create the column/row headings using the code below. I would really appreciate some help getting the code for the grade bit.

<table>
<tr>
    <th>Pupil name</th>
    <?php
        $selectlesson=$connect->query("SELECT DISTINCT lessonid FROM `grades` ");
  while($rowslesson=$selectlesson->fetch_array())
    {
    ?>
    <th><?php echo $rowslesson['lessonid']; ?></th>
    <?php
    }
    ?>

</tr>
<?php
        $selectpupil=$connect->query("SELECT DISTINCT pupilid FROM `grades` ");
  while($rowspupil=$selectpupil->fetch_array())
    {
    ?>
    <tr>
    <td><?php echo $rowspupil['pupilid']; ?></td>
    </tr>
    <?php
    }
    ?>

Thanks in advance for your help.

Answer 1

Actually I figured out how to do it. I repeated the SQL search in the TD and it seemed to work

<table>
<tr>
    <th>Pupil name</th>
    <?php
        $selectlesson=$connect->query("SELECT DISTINCT lessonid FROM `grades` ");
  while($rowslesson=$selectlesson->fetch_array())
    {
    ?>
    <th><?php echo $rowslesson['lessonid']; ?></th>
    <?php
    }
    ?>

</tr>
<?php
        $selectpupil=$connect->query("SELECT DISTINCT pupilid FROM `grades` ");
  while($rowspupil=$selectpupil->fetch_array())
    {
    ?>
    <tr>
        <td><?php $pupil= $rowspupil['pupilid'];
            echo $rowspupil['pupilid']; ?>
        </td>
        <?php
        $selectlesson=$connect->query("SELECT DISTINCT lessonid FROM `grades` ");
            while($rowslesson=$selectlesson->fetch_array())
                {
                ?>
        <td><?php $lessongrade = $rowslesson['lessonid']; 

            $selectgrade=$connect->query("SELECT * FROM `grades` where lessonid ='$lessongrade' and pupilid = '$pupil' LIMIT 1 ");
            while($rowsgrade=$selectgrade->fetch_array())
            {
            echo    $rowsgrade ['grade'];
            }?>
        </td>
        <?php
        }
        ?>
    </tr>
    <?php
    }
    ?>



</tr>