Jump to content
#StayAtHome ×
gabucknall

Pivot table with dates as column headers

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?

Edited by gabucknall
I didn't finish entering the question

Share this post


Link to post
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 ?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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

Edited by Barand

Share this post


Link to post
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>

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.