将行添加到具有动态列的 HTML 表中

我正在使用下面的代码（作为上一个问题的解决方案提供将 mySQL 记录显示为 HTML 表列 https://stackoverflow.com/questions/21870246/display-mysql-records-as-html-table-columns) 生成一个 HTML 表，例如：

根据这些表：

周名称位于表“week”中，每周记录还包含该周的会话数：

+---------+-----------+----------+-----------+
| week_pk | week_name | sessions | cohort_fk |
+---------+-----------+----------+-----------+
|       1 | Week 1    |        3 |         1 |
|       2 | Week 2    |        2 |         1 |
|       3 | Week 3    |        1 |         1 |
+---------+-----------+----------+-----------+

+-----------+-------------+-------------+-------------+
| cohort_pk | cohort_name | cohort_code | cohort_year |
+-----------+-------------+-------------+-------------+
|         1 | Some name   | MICR8976    |        2014 |
+-----------+-------------+-------------+-------------+ 

我现在想扩展它，以将与会者显示为会话行下的附加表行，并指示他们参加了哪个会话。出席表为：

+---------------+-------------+---------+-----------+---------+---------+
| attendance_pk | given_names | surname | cohort_fk | week_fk | session |
+---------------+-------------+---------+-----------+---------+---------+
|             1 | Bill        | Smith   |       1   |       2 |       2 |
|             2 | Fred        | Jones   |       1   |       1 |       1 |
+---------------+-------------+---------+-----------+---------+---------+

生成的 HTML 表格将类似于：

无论如何，请帮助修改下面的代码以获得如上图所示的结果。

$cohort = '1';
$year = '2014';

    $query = "SELECT * FROM cohort, week, attendance 
    WHERE week.cohort_fk = cohort.cohort_pk 
    AND attendance.week_fk = week.week_pk
    AND attendance.cohort_fk = cohort.cohort_pk
    AND cohort.cohort_year = '$year' 
    AND cohort.cohort_pk = '$cohort'";

    $result = mysql_query($query, $connection) or die(mysql_error());

    echo "<table border='1'>";
    echo "<tr><td>Name</td>";
    $second_row = "<tr><td>Session</td>";
    while($row = mysql_fetch_assoc($result)){
        $weekname  = $row["week_name"];
        $n_session = $row["sessions"];
        echo "<td colspan='$n_session'>$weekname</td>";
        for($i=1; $i<=$n_session; $i++){
            $second_row .= "<td>S$i</td>";
        }
    }
    echo "</tr>";
    echo "$second_row</tr>";
    echo "</table>";
    ?>

以下代码可能是最简单且最接近您想要的代码。

• mysqli_*使用函数。
• 包括两个查询。
• 第一个查询仅检索来自cohort and week.
• 在第一个while loop $weeksession数组已创建。它保存给定的列号周数 and 会话数.

$year = 2014;
$cohort = 1;

$query = "SELECT * FROM cohort, week 
WHERE week.cohort_fk = cohort.cohort_pk 
AND cohort.cohort_year = '$year' 
AND cohort.cohort_pk = '$cohort'
ORDER BY week.week_pk";

$dblink = mysqli_connect("localhost", "root", "", "test");
$result = mysqli_query($dblink, $query);

echo "<table border='1'>";
echo "<tr><td>Name</td>";
$second_row = "<tr><td>Session</td>";
$totalcolumn = 1;                               
while( $row = mysqli_fetch_assoc($result) ){
    $weekname   = $row["week_name"];
    $n_session  = $row["sessions"];
    $weekpk     = $row["week_pk"];              
    $totalcolumn += $n_session;                 
    echo "<td colspan='$n_session'>$weekname</td>";
    for($i=1; $i<=$n_session; $i++){
        $second_row .= "<td>S$i</td>";
        $weeksession[$weekpk][$i] = $totalcolumn - $n_session + $i;
    }
}//end while
echo "</tr>";
echo $second_row . "</tr>";


$query = "SELECT * FROM cohort, week, attendance 
WHERE week.cohort_fk = cohort.cohort_pk 
AND attendance.week_fk = week.week_pk
AND attendance.cohort_fk = cohort.cohort_pk
AND cohort.cohort_year = '$year' 
AND cohort.cohort_pk = '$cohort'
ORDER BY attendance.attendance_pk";
$result = mysqli_query($dblink, $query);
while( $row = mysqli_fetch_assoc($result) ){
    $name   = $row["given_names"] . " " . $row["surname"];
    $weekpk     = $row["week_pk"];
    $sno        = $row["session"];
    echo "<tr><td>$name</td>";
    for($i=2; $i<=$totalcolumn; $i++){      
        if( $weeksession[$weekpk][$sno] == $i )
            echo "<td>X</td>";
        else
            echo "<td>-</td>";              
    }                                       
    echo "</tr>";
}//end while
echo "</table>";

• 如果一个人参加了 2 个或更多会议，那么它将显示在多行中