Jump to content

Pivot table with dates as column headers


gabucknall

Recommended Posts

 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?

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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.

register.PNG

Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.