Pivot table with dates as column headers

[gabucknall]

 am developing a registering system as part of a mySQL database. The relevant table is called register and has as some of its fields - fullname (ie the name of the attendee), present (answered either Y or N), registerdate (the date of the lesson) and projectcode (links to a project table - in this system a project is a course). I want to get out a report which has the lesson dates as the column headers and the student names as the row headers so it looks something like:

               |1/3/2018 | 6/3/2018 | 12/3/2018 | etc
Adam    |Y                  | N            | Y                    |
Sue        |Y                 | Y             |Y

and so on.

I have a  simple query as below 

    SELECT fullname, registerdate, present FROM register 

Just gets me a list. I have looked at lots of pivot table examples but none have a date as the X axis headers. The lessons are irregular and  aren't on various dates/days of the week.

Can anyone offer some help please?

Edited June 1, 2018 by gabucknall
I didn't finish entering the question

[Barand]

Would the dates just be those from the records, or could there be dates that should be shown (eg holiday) even though there are no records for that date?

Would the dates be from the register table or from another table holding course dates?

 

What code have you tried ?

[gabucknall]

The dates would be those in the table not a regular set of dates.

I've only tried the simple query -     SELECT fullname, registerdate, present FROM register  - as I've no idea how to get started.

[Barand]

I would set it up as shown in the data model. The enrolment table would tell you who should have attended each course and the course_dates would give the the dates the courses were scheduled. These two tables give you the list of students and the list of dates for the row and column headings for each course.

You now know who *should* have attended each course and when. You need now only store actual attendances in the register. Query it and drop the "Y" values into the appropriate cells of the output table.

Edited June 2, 2018 by Barand

[Barand]

Get the dates first. You use them as headers and also as keys for an array of attendances for each student.

I would not attempt to create the pivot table purely in SQL. Given what you have, I'd do it like this

<?php
$course = $_GET['course'] ?? '';

/****************************
**    GET COURSE DATES     **
*****************************/
$stmt = $db->prepare("SELECT DISTINCT registerdate
                       FROM register2
                       WHERE projectcode = ?
                       ORDER BY registerdate          
                       ");
$dates = [];
$temparray = [];
$stmt->execute( [$course] );
foreach ($stmt as $rec) {
    $dt = $rec['registerdate'];
    $temparray[$dt] = 'N';                    // template array to store each student's attendance
    $dates[] = date('M jS', strtotime($dt));  // array of heading dates
}
$kdates = count($dates);
$theads = "<tr><th rowspan='2'>Student</th><th colspan='$kdates'>Attendance</th></tr>
        <tr><th>" . join('</th><th>', $dates) . "</th></tr>\n";
        
/*****************************************************************
** GET THE REGISTER RECORDS FOR THE SELECTED COURSE
**   - CREATE A BLANK ATTENDANCE ARRAY FOR EACH USING THE TEMPARRAY
**     THEN STORE THE PREENT VALUE INTO THE TEMPARRAY BY DATE
******************************************************************/
$stmt = $db->prepare("SELECT fullname
                           , present
                           , registerdate
                        FROM register2
                        WHERE projectcode = ?
                        ORDER BY fullname, registerdate
                        ");
$stmt->execute( [$course] );
$students = [];
foreach ($stmt as $rec) {
    if ( !isset($students[$rec['fullname']])) {
        $students[$rec['fullname']] = $temparray;
    }
    $students[$rec['fullname']][$rec['registerdate']] = $rec['present'];
}

/***********************************************************
** OUTPUT THE STUDENTS ARRAY
************************************************************/
$tdata = '';
foreach ($students as $n => $att) {
    $tdata .= "<tr><td class='name'>$n</td>";
    foreach ($att as $pres) {
        $cls =  ($pres=='N') ? "class='absent'" : '';
        $tdata .= "<td $cls>$pres</td>";
    }
    $tdata .= "</tr>\n";
}  
 
/******************************************************************************************************
* function to get the options for course selection
* 
* @param PDO $db          pdo connection
* @param string $course   current course code
*/
function course_options($db, $course)
{
    $res = $db->query("SELECT code, name
                        FROM course
                        ORDER BY name");
    $opts = "<option value=''>- Select course -</option>\n";
    foreach ($res as $crs) {
        $sel = $crs['code']==$course ? 'selected' : '';
        $opts .= "<option $sel value='{$crs['code']}'>{$crs['name']}</option>\n" ;
    }
    return $opts;
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)">
<meta name="author" content="Barand">
<meta name="creation-date" content="06/02/2018">
<title>Attendance Register</title>
<style type="text/css">
    body {
        font-family: verdana;
        font-size: 10pt;
    }
    #title {
        background-color: black;
        color: white;
        font-size: 16pt;
        font-weight: 600;
        text-align: center;
        padding: 10px;
    }
    #form {
        padding: 10px;
    }
    table {
        width: 60%;
        margin-left: auto;
        margin-right: auto;
        border-collapse: collapse;
    }
    th {
        background-color: #396;
        color: white;
        padding: 4px;
        border-color: white;
        font-size: 10pt;
    }
    td {
        padding: 4px;
        text-align: center;
    }
    td.name {
        text-align: left;
    }
    .absent {
        background-color: #FFC0C0;
    }
</style>
</head>
<body>
<div id='title'>
    Attendance Register
</div>
<div id='form'>
    <form>
        Course <select name='course'><?=course_options($db, $course)?></select>
        <input type="submit" name="btnSub" id="btnSub" value="Submit">
        <hr>
    </form>
</div>
<?php
    if ($course) {
?>
        <table border="1">
        <?=$theads?>
        <?=$tdata?>
        </table>

<?php
    }
?>
</body>
</html>

 

[gabucknall]

Thank you. I will try that and let you know.