53020396 how-to-display-grouped-data-in-separate-tables-with

<?php // 53020396/how-to-display-grouped-data-in-separate-tables-with-a-php-loop

/* ---------------------------------------------------------------------------
 * The input stream consists of an Ordered Iteration of:
 *    A collection of Individual Records for each Student (StudentRecordGoup)
 *
 *  Each StudentRecordGoup consists of a Sequence of:
 *     Start of Group
 *     Iteration of Student records belonging to the group
 *     End of Group
 *
 *   Notice: There is no 'IF' statement anywhere in the control logic for a group!
 *
 *   Please note: There is exactly one place in the code to assign any appropriate action!
 *                i.e. The structure of the code exactly matched the structure of the data. :)
 *
 *   This makes it easier to debug, maintain and amend?
 *
 *   To do this we need 'state' information. Specifically that a record is part
 *   of the current 'student record group' group or not. How do we do this?
 *
 *   We always need a record to test! Including at the start! We never read a record
 *   and then decide what to do with it. We always know where we are in the data
 *   structure and either the current record belongs to the group or not.
 *
 *    We need to use a technique called 'Read Ahead'. Literally, There is always
 *    a record to process. You don't have to test it to know where you are.
 *    Once you process it then you immediately read the next record from the input.
 *    i.e. You end up reading a new record NOT JUST AT THE WND OF THE LOOP!
 *    You cannot use 'foreach' loops.
 *
 *   We have to include Initialisation to setup code and any termination code.
 *
 *   I will put all the 'action place holders' in functions. That way it avoids
 *   obscuring the high-level logic.
 */

// Get the ordered student detail records
$pdo = getPdoConnection();
$pdoResultSet = prepareAndExecuteQuery($pdo);


// Process all the students Record Groups  - 'read ahead' of the row
$curStudentRecord = $pdoResultSet->fetch();  // is assoc array

while ($curStudentRecord !== false)  { // process the student record groups

   // process one complete student group...
   $curStudentRecordGroupId = $curStudentRecord['sid'];
   startStudendRecordGroup($curStudentRecordGroupId);
   while (   $curStudentRecord !== false  // check record belongs to the current group
          && $curStudentRecord['sid'] === $curStudentRecordGroupId) {

        processStudentRecord($curStudentRecord);
        $curStudentRecord = $pdoResultSet->fetch();  // read the next record
    }

   endStudendRecordGroup($curStudentRecordGroupId); // ignore the current record
                                                    // that is the next group!
}
// all groups have been processed
exit;

/* --------------------------------------------------------------------------
 * Student record group processing
 */
function startStudendRecordGroup($curStudentRecordGroupId)
{
    echo "<!-- new group: sid = $curStudentRecordGroupId -->";
    echo "<table><tr><th>SID</th><th>Date</th><th>FName</th><th>LName</th>
                     <th>activity</th><th>time</th><th>score</th></tr>";
}

function processStudentRecord($curStudentRecord)
{
    echo "<!-- group record: sid = {$curStudentRecord['sid']} -->";
    echo "<tr>
                    <td>" . $curStudentRecord['sid'] . "</td>
                    <td>" . $curStudentRecord['fname'] . "</td>
                    <td>" . $curStudentRecord['lname'] . "</td>
                    <td>" . $curStudentRecord['col3'] .  "</td>
                    <td>" . $curStudentRecord['col4'] .  "</td>
                    <td>" . $curStudentRecord['col4'] .  "</td>
                </tr>";
}

function endStudendRecordGroup($curStudentRecordGroupId)
{
    echo "<!-- end group: sid = $curStudentRecordGroupId -->";
    echo "</table>";
}

/* --------------------------------------------------------------------------
 * Database access
 */

// Execute query and return 'resultset'
function prepareAndExecuteQuery(\PDO $pdo)
{
   $sql = 'SELECT id, sid, fname, lname, col3, col4, col5
           FROM activity
           ORDER BY sid, id';

   $stmt = $pdo->prepare($sql);
   $allOk = $stmt->execute();
   return $stmt;
}

// DB Connection
function getPdoConnection()
{
    $opt  = array(
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_EMULATE_PREPARES   => false,
            );

    $pdo = new \PDO('mysql:host=localhost;dbname=notmydb;', 'notme', 'notmypass', $opt);
    return $pdo;
}