Adding rows to a HTML table with dynamic columns

Question

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:

enter image description here

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:

enter image description here

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>";
    ?>

in your sample attendance table you have `cohort_pk` and in your query you have `cohort_fk` ... should be `cohort_fk` in the table ... right? — Tun Zarni Kyaw, Feb 19 '14 at 05:32

score 1 · Accepted Answer · answered Feb 19 '14 at 08:11

The following code might be the simplest & nearest to what you want.

mysqli_* functions are used.
Two queries are included.
The first query retrieves only from cohort and week.
In the first while loop $weeksession array is created. It holds the column number for given week number and session number.

$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>";

If a person attends 2 or more sessions, then it will be shown in multiple rows

Works perfectly as I need it to. Thanks again for your prompt help with my questions! — IlludiumPu36, Feb 20 '14 at 01:30
Hi Tun...rather than having multiple rows for each attended session, I want to update the cells in the same row. I have opened a new question on this http://stackoverflow.com/questions/22342191/dynamic-htlm-table-updating-cell-rows — IlludiumPu36, Mar 12 '14 at 05:21

score 1 · Answer 2 · answered Feb 19 '14 at 09:55

maybe it helps.

<?php

$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'";
$result = mysql_query($query, $connection) or die(mysql_error());

$rows = array();
$weeks = array();
$sessions = array();
while ($rows[] = $row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $weeks[] = "<td colspan='" . (int) $row['sessions'] . "'>" . $row['week_name'] . "</td>";
    for ($i = 1; $i <= (int) $row['sessions']; $i++) {
        $sessions[] = "<td>S" . $i . "</td>";
    }
}

$attendance = array();
$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 = mysql_query($query, $connection) or die(mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $attendance[] = "<tr><th>" . $row['given_names'] . " " . $row['surname'] . "</th>";
    foreach ($rows as $week) {
        for ($i = 1; $i <= (int) $week['sessions']; $i++) {
            if ($row['week_fk'] == $week['week_pk'] && $row['session'] == $i) {
                $attendance[] = "<td>X</td>";
            } else {
                $attendance[] = "<td></td>";
            }
        }
    }
    $attendance[] = "</tr>";
};

$table = array(
    "<table width = 50% border = '1' cellspacing = '2' cellpadding = '0'>",
    "<tr><th>Name</th>", join('', $weeks), "</tr>",
    "<tr><th>Sessions</th>", join('', $sessions), "</tr>",
    join('', $attendance),
    "</table>"
);

echo join("", $table);

Adding rows to a HTML table with dynamic columns

2 Answers2

Linked