Dynamic HTML table - Updating cell rows

Question

I have an HTML table being dynamically generated from database. The solution for achieving this can be seen at this question Adding rows to a HTML table with dynamic columns.

This works fine, except I want to indicate all sessions a person attended for each week in the same row - with the code below, an additional session attendance becomes another row appended to the HTML table. So what I want is like:

DB tables are like ('week', 'cohort' and 'attendance' tables)

+---------+-----------+----------+-----------+
| 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 | person_id | given_names | surname | cohort_fk | week_fk | session |
+---------------+-----------+-------------+---------+-----------+---------+---------+
|             1 |    123456 | Bill        | Smith   |         1 |       1 |       2 |
|             2 |    123456 | Bill        | Smith   |         1 |       2 |       2 |
|             3 |    753354 | Fred        | Jones   |         1 |       1 |       1 |
|             4 |    753354 | Fred        | Jones   |         1 |       2 |       1 |
|             5 |    753354 | Fred        | Jones   |         1 |       3 |       1 |
+---------------+-----------+-------------+---------+-----------+---------+---------+

And the code that I'm using:

$cohort = $_POST['cohort'];
$year = $_POST['year'];


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

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

echo "<table width='100%' cellpadding='4' cellspacing='0'  class='attendance_table'>";
echo "<tr><td class='theadings'></td>";
$second_row = "<tr><td class='theadings'></td>";
$totalcolumn = 1;                               
while( $row = mysql_fetch_assoc($result) ){
    $weekname   = $row["week_name"];
    $n_session  = $row["sessions"];
    $weekpk     = $row["week_pk"];              
    $totalcolumn += $n_session;                 
    echo "<td class='theadings' colspan='$n_session'>$weekname</td>";
    for($i=1; $i<=$n_session; $i++){
        $second_row .= "<td class='theadings_lab'>Lab $i</td>";
        $weeksession[$weekpk][$i] = $totalcolumn - $n_session + $i;
    }
}
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 = mysql_query($query, $connection) or die(mysql_error());
while( $row = mysql_fetch_assoc($result) ){
    $name = $row["given_names"] . " " . $row["surname"];
    $weekpk = $row["week_pk"];
    $sno = $row["session"];
    echo "<tr><td class='tborder_person_left'>$name</td>";
    for($i=2; $i<=$totalcolumn; $i++){      
        if( $weeksession[$weekpk][$sno] == $i )
            echo "<td class='tborder_person_attended'>&#10004</td>";
        else
            echo "<td class='tborder_person'>-</td>";              
    }                                       
    echo "</tr>";
}//end while
echo "</table>";

@Kickstart below is an example of what the table looks like with your code. You can see for example Melody Chew and Kit Yeng Melody Chew (same person) have two seperate rows. The unique identifier needs to be on the person_id which exists in the attendance table (apologies for not showing this before! my BAD Note also the additional columns on the right of the table with the crosses which should be under the week 2 column.

Answer 1

I personally would go through nested arrays.

I give you an example. Note that this is only in principle, as I do not have the database, I have not tested. :)

1 -> statement of the array $liste_name = array();

2 -> Replace your second treatment by :

while( $row = mysql_fetch_assoc($result) ){

    $name = $row["given_names"] . " " . $row["surname"];
    $weekpk = $row["week_pk"];
    $sno = $row["session"];
    $person_id = $row["person_id"]; //add person_id


    $liste_name[$person_id] = $name; // to have the last name
    $tab[$person_id][1] = "<td class='tborder_person_left'>$name</td>"; // to have the last name

    for($i=2; $i<=$totalcolumn; $i++){

        if( $weeksession[$weekpk][$sno] == $i )
            $tab[$person_id][$i] = "<td class='tborder_person_attended'>&#10004</td>";              
    }
}//end while

3 -> Fill the empty areas :

foreach ($liste_name as $person_id => $name){

    $tab2[$person_id][1] = $tab[$person_id][1]; 

    for($i=2; $i<=$totalcolumn; $i++){
        if (!isset($tab[$person_id][$i]) || ($tab[$person_id][$i] != "<td class='tborder_person_attended'>&#10004</td>"))
            $tab[$person_id][$i] = "<td class='tborder_person'>-</td>";
        $tab2[$person_id][$i] = $tab[$person_id][$i]; 
    }
}

4 -> Use the array :

foreach($tab2 as $person_id => $col){
    echo "<tr>";
    foreach ($col as $i => $value){
        echo $value;
    }
    echo "</tr>";
}

echo "</table>";

I try with fictional data and it works. :)

Answer 2

I would be tempted to use a couple of CROSS JOINs to get all the possible combinations of session / week / person, and then left join that against attendance.

SELECT unique_names.given_names, 
        unique_names.surname,
        week_sessions.week_pk, 
        week_sessions.session,
        attendance.attendance_pk
FROM
(
    SELECT week_pk, sub1.i AS session, week_name, week.cohort_fk, cohort.cohort_year 
    FROM week
    INNER JOIN cohort 
    ON week.cohort_fk = cohort.cohort_pk
    INNER JOIN
    (
        SELECT 1 i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
    ) sub1
    ON week.sessions >= sub1.i
    WHERE cohort.cohort_year = 2014 
    AND cohort.cohort_pk = 1
) week_sessions
CROSS JOIN
(
    SELECT DISTINCT given_names, surname 
    FROM attendance
) unique_names
LEFT OUTER JOIN attendance
ON week_sessions.week_pk = attendance.week_fk
AND week_sessions.cohort_fk = attendance.cohort_fk
AND week_sessions.session = attendance.session
AND unique_names.given_names = attendance.given_names
AND unique_names.surname = attendance.surname
ORDER BY unique_names.given_names, 
        unique_names.surname,
        week_sessions.week_pk, 
        week_sessions.session

I have knocked up an SQL fiddle for this:-

http://www.sqlfiddle.com/#!2/4a388/7

You can then loop around this easily (although adding the titles is a bit messy - following would be half the size without the titles). I have used the mysql_* functions as that is what you are already using.

<?php

$connection = mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("test_area") or die(mysql_error());

$sql = "SELECT unique_names.person_id,
                unique_names.FullName,
                week_sessions.week_pk, 
                week_sessions.session,
                attendance.attendance_pk
        FROM
        (
            SELECT week_pk, sub1.i AS session, week_name, week.cohort_fk, cohort.cohort_year 
            FROM week
            INNER JOIN cohort 
            ON week.cohort_fk = cohort.cohort_pk
            INNER JOIN
            (
                SELECT 1 i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
            ) sub1
            ON week.sessions >= sub1.i
            WHERE cohort.cohort_year = 2014 
            AND cohort.cohort_pk = 1
        ) week_sessions
        CROSS JOIN
        (
            SELECT person_id, MAX(CONCAT_WS(' ', given_names, surname)) AS FullName
            FROM attendance
            GROUP BY person_id
        ) unique_names
        LEFT OUTER JOIN attendance
        ON week_sessions.week_pk = attendance.week_fk
        AND week_sessions.cohort_fk = attendance.cohort_fk
        AND week_sessions.session = attendance.session
        AND unique_names.person_id = attendance.person_id
        ORDER BY unique_names.person_id,
                unique_names.FullName,
                week_sessions.week_pk, 
                week_sessions.session";

$result = mysql_query($sql, $connection) or die(mysql_error());
$prev_person_id = 0;
$first = true;
$header = array('Name'=>array('Session'));
$output = array();
while( $row = mysql_fetch_assoc($result) )
{
    if ($prev_person_id != $row['person_id'])
    {
        if ($prev_person_id != 0)
        {
            $first = false;
        }
        $prev_person_id = $row['person_id'];
        $output[$prev_person_id] = array();
    }
    if ($first)
    {
        $header["Week ".$row['week_pk']]["S".$row['session']] = "S".$row['session'];
    }
    $output[$prev_person_id][] = (($row['attendance_pk'] == '') ? '&nbsp;' : 'X');
}

$header1 = '';
$header2 = '';
foreach($header as $key=>$value)
{
    $header1 .= "<td colspan='".count($value)."'>$key</td>\r\n";
    foreach($value as $key1=>$value1)
    {
        $header2 .= "<td>$value1</td>\r\n";
    }
}
echo "<table border='1'>\r\n";
echo "<tr>\r\n$header1</tr>\r\n";
echo "<tr>\r\n$header2</tr>\r\n";

foreach($output as $name=>$value)
{
    echo "<tr><td>$name</td>";
    foreach($value as $key1=>$value1)
    {
        echo "<td>$value1</td>\r\n";
    }
    echo "</tr>";
}

echo "</table>\r\n";

?>