I'm using the code below (provided as a solution to a previous question at Display mySQL records as HTML table columns) to generate an HTML table like:
Based on these tables:
The week names are in table 'week' and each week record also contains the number of sessions for that 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 |
+-----------+-------------+-------------+-------------+
I now want to extend this to show attendees as additional table rows under the session row and indicate which session they attended. The attendance table is:
+---------------+-------------+---------+-----------+---------+---------+
| attendance_pk | given_names | surname | cohort_fk | week_fk | session |
+---------------+-------------+---------+-----------+---------+---------+
| 1 | Bill | Smith | 1 | 2 | 2 |
| 2 | Fred | Jones | 1 | 1 | 1 |
+---------------+-------------+---------+-----------+---------+---------+
The resulting HTML table would be like:
Anyway help on modifying the code below to get results as per the above image appreciated.
$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>";
?>