Pivot Table or Cross Tab in PHP using MYSQL for attendance

akshayhomkar · January 15, 2014

Hello

I want to create attendance sheet on which date are printed as column and name of student/staff as column

database is as shown

CREATE TABLE IF NOT EXISTS `attendance` (
`date` varchar(500) DEFAULT NULL,
`time` varchar(1000) DEFAULT NULL,
`staffname` varchar(1000) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`role` varchar(1000) NOT NULL,
`status` varchar(1) DEFAULT NULL
)

but when I querying the table repeating the names of student and staff which not gives me report as expected here I attaching code also

<table align="letf" style="margin-left: 0px; border: 1px solid black; border-spacing: 0px;" width="8">
        <th style="border: 1px solid black; text-align: center;">Date</th>
        <?php
         $sql133="select distinct date from attendance";
       $sql_row133=mysqli_query($dbConn,$sql133);
       while($sql_res133=mysqli_fetch_assoc($sql_row133))       {

       $date=$sql_res133["date"];
             ?>
       <th style="border: 1px solid black; text-align: center;">
       <?php echo $date; ?>
   </th>
                <?php

      $a=$date;
                $sql13="
SELECT   atten.date,atten.time,atten.staffname,atten.id, atten.status, supst.id, supst.staffname
    FROM
     (examcenter.attendance atten INNER JOIN examcenter.supportstaff supst ON
         atten.id = supst.id) where atten.date='$a'    group by supst.staffname,supst.id ORDER BY
    atten.id ASC    ";
                 $sql_row13=mysqli_query($dbConn,$sql13);
             while($sql_res13=mysqli_fetch_assoc($sql_row13))      {

             $staffname=$sql_res13["staffname"];
         $status=$sql_res13["status"];
                                       ?>

        <tr>
           <td><?php echo $staffname; ?></td>
        <td><?php echo $status; ?></td>

    <?php
       }
       }

     ?>

</table>

please guide what do to create a report as expected

Name/Date

12-11-2013

13-11-2013 16-11-2013 Student name1 P A A Staffname 1 P P A

awaiting valuable reply

Barand · January 16, 2014

Here's my method for reports like that

$sql = "SELECT DISTINCT date 
        FROM attendance
        ORDER BY DATE";
$res = $db->query($sql);   // mysqli query
while ($row = $res->fetch_row()) {
    $dates[] = $row[0];
}
/***********************************
* Table headings                   *
************************************/
$emptyRow = array_fill_keys($dates,'');
// format dates
foreach ($dates as $k=>$v) {
    $dates[$k] = date('d-M', strtotime($v));
}
$heads = "<table border='1'>\n";
$heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th></tr>\n";

/***********************************
* Main data                        *
************************************/
$sql = "SELECT date, staffname, status
        FROM attendance
        ORDER BY staffname";
$res = $db->query($sql);
$curname='';
$tdata = '';
while (list($d, $sn, $s) = $res->fetch_row()) {
    if ($curname != $sn) {
        if ($curname) {
            $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n";
        }
        $rowdata = $emptyRow;
        $curname = $sn;
    }
    $rowdata[$d] = $s;
}
$tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n";
$tdata .= "</table\n";
?>
<html>
<head>
<style type="text/css">
td {
    text-align: center;
}
table {
    border-collapse:collapse;
}
</style>
</head>
<body>
    <?php
        echo $heads;
        echo $tdata;
    ?>
</body>
</html>

Your dates (dd-mm-yyyy) are unusable in a database. Store as type DATE format YYYY-MM-DD so they can be correctly sorted or compared.

akshayhomkar · January 18, 2014

Thanks it works as expected

akshayhomkar · January 18, 2014

Sir please guide how to calculate number presents and absent

awaiting your valuable reply

Barand · January 18, 2014

Do you mean absent and present on each date?

akshayhomkar · January 18, 2014

yes sir absent and present on each date

Barand · January 18, 2014

Added a few lines

<?php

$sql = "SELECT DISTINCT date 
        FROM attendance
        ORDER BY DATE";
$res = $db->query($sql);   // mysqli query
while ($row = $res->fetch_row()) {
    $dates[] = $row[0];
}
/***********************************
* Table headings                   *
************************************/
$emptyRow = array_fill_keys($dates,'');

// create arrays for "absent" and "present"
$present = $absent = array_fill_keys($dates, 0);           // ADD LINE

// format dates
foreach ($dates as $k=>$v) {
    $dates[$k] = date('d-M', strtotime($v));
}
$heads = "<table border='1'>\n";
$heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th></tr>\n";

/***********************************
* Main data                        *
************************************/
$sql = "SELECT date, staffname, status
        FROM attendance
        ORDER BY staffname";
$res = $db->query($sql);
$curname='';
$tdata = '';
while (list($d, $sn, $s) = $res->fetch_row()) {
    if ($curname != $sn) {
        if ($curname) {
            $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n";
        }
        $rowdata = $emptyRow;
        $curname = $sn;
    }
    $rowdata[$d] = $s;
    switch ($s) {                       // ADD THIS SWITCH STATEMENT
        case 'P' :
            $present[$d]++;
            break;
        default:
            $absent[$d]++;
    }
}
$tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n";

// present and absent totals                // ADD THESE LINES
$tdata .= "<tr><td>PRESENT</td><td>" . join('</td><td>', $present). "</td></tr>\n";
$tdata .= "<tr><td>ABSENT</td><td>" . join('</td><td>', $absent). "</td></tr>\n";

$tdata .= "</table\n";
?>

akshayhomkar · January 18, 2014

thank you very much sir

akshayhomkar · January 19, 2014

thats run much pretty but i want to display it with staffname not by date i changed it $present[$d]++; to $present[$sn]++; it runs but not showing on the same line as staffname

awaiting your valuable reply

Barand · January 19, 2014

You need to add keys "P" and "A" to the $emptyRow array and increment those.

$rowdata[$s]++;

You'll also need to add the two headings to the table headings row.

Edited January 19, 2014 by Barand

akshayhomkar · January 19, 2014

Thanks sir working now but shows an notice undefined index which defined in switch statement

Barand · January 19, 2014

I expected you would remove the lines you added last time since you no longer want the totals by date, despite your earlier post:

Do you mean absent and present on each date?

yes sir absent and present on each date

:facewall:

Barand · January 19, 2014

it should now look something like this

$sql = "SELECT DISTINCT date 
        FROM attendance
        ORDER BY DATE";
$res = $db->query($sql);   // mysqli query
while ($row = $res->fetch_row()) {
    $dates[] = $row[0];
}
/***********************************
* Table headings                   *
************************************/
$emptyRow = array_fill_keys($dates,'');
$emptyRow['P'] = 0;
$emptyRow['A'] = 0;

// format dates
foreach ($dates as $k=>$v) {
    $dates[$k] = date('d-M', strtotime($v));
}
$heads = "<table border='1'>\n";
$heads .= "<tr><th>Name</th><th>" 
            . join('</th><th>', $dates) 
            . "</th><th>Present</th><th>Absent</th></tr>\n";

/***********************************
* Main data                        *
************************************/
$sql = "SELECT date, staffname, status
        FROM attendance
        ORDER BY staffname";
$res = $db->query($sql);
$curname='';
$tdata = '';
while (list($d, $sn, $s) = $res->fetch_row()) {
    if ($curname != $sn) {
        if ($curname) {
            $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n";
        }
        $rowdata = $emptyRow;
        $curname = $sn;
    }
    $rowdata[$d] = $s;
    $rowdata[$s]++;
}
$tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n";

$tdata .= "</table\n";
?>
<html>
<head>
<style type="text/css">
td,th {
    text-align: center;
    padding: 5px;
}

table {
    border-collapse:collapse;
}
</style>
</head>
<body>
    <?php
        echo $heads;
        echo $tdata;
    ?>
</body>
</html>

akshayhomkar · January 19, 2014

Thank you very much all done

Edited January 19, 2014 by akshayhomkar

akshayhomkar · January 19, 2014

Thank you very much SIR all done

chandaret · May 24, 2015

Thanks follow your step i solv my problem too

Ritika · March 11, 2016

i do not know why but I am getting ERROR: Parse error: syntax error, unexpected '$sql' (T_VARIABLE) in C:\xampp\htdocs\att\creating_column\final3.php on line 3.

Its in select query statement.. please help thanks

Barand · March 11, 2016

I don't know what you have on on lines 1 and 2 but suspect you may be missing ";" at end of a line.

Ritika · March 11, 2016

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Q2220229 - Pivot table</title>
    <style>
      td {
        border-bottom: 1px solid grey;
        width: 10em;
      }
    </style>
  </head>

  <body>
<?php

  // I am getting output like 
/*

 * Date     |NAME      | Roll_no  |  ATTEND
   ========================================
   01/02/14 |Musician  |   1     |    1
   01/02/14 |Leader    |   2     |    1
   01/02/14 |Singer    |   3     |    0
   08/02/14 |Musician  |   4     |    0
   08/02/14 |Leader    |   5     |    1
   08/02/14 |Singer    |   6     |    1
 *
 */
// DESIRED OUTPUT 

 /* NEEDED sample output:
  *
  * Roll_no |   NAME     |01/02/14  |08/02/14
    ===============================
       1    |Musician    |   0      |  1
       2    |Leader      |   1      |  1
       3    | Singer     |   1      |  0
  */

  
$db = mysql_connect('localhost', 'test', 'test', 'testmysql');

// 1) Must return three columns only.
// 2) Can return any number of 'roles' - one per row
// 3) Any date range but beware you may need a wide page!
// 4) Must sort by date!  
$query = mysql_query( "SELECT date, attend, name FROM atten ORDER BY date ASC, name ASC");

// i prefer to used named subscripts to make the code easier to read.
// These MUST match up with column alias from the above query!
define('THE_DATE', 'date'); // !important
define('name',     'name');         // !imortant
define('attend',   'attend');       // !important

/*
 * Now, we need a complete array of Roles in the order that they are to be displayed.
 *
 * These names must match with the names of the roles in the input data.
 * They will be printed out in the order that they appear in the array.
 *
 * These are the only roles that will appear in the $outputDates array.
 * Add more and in any order to control which 'roles' are shown.  
 *
 */
$allRoles = array('student1', 'student5', 'student6', 'student2' ); // !important

/*
 * At some point we will need an output array that we can easily traverse and
 * print out as a row of dates. i.e. a 'page' of data.
 *
 * We will build it up as we go along...
 */
$outputDates = array(); // !important -- this is the 'pivoted' output array

/*
 * Start to process the input data.
 *
 * To make my life easier, i will use the 'read ahead' technique to simplify the code.
 */

$currentInputRow = mysql_fetch_array($query);

while (isset($currentInputRow[THE_DATE])) { // process all the input array...

  // must be a new day...
  $currentDay = $currentInputRow[THE_DATE];

  // create an array to hold ALL the possible roles for this day...
  $theDayRoles = array();

  // initialise the array with default values for all the requested roles.
  foreach ($allRoles as $name) {
    $theDayRoles[$name] = '--';
  }

  // now we need to fill theDayRoles with what we actually have for the current day...
  while ($currentInputRow[THE_DATE] == $currentDay) { // loop around all records for the current day

    // set the appropiate DayRole to the current ATTEND
    $theDayRoles[$currentInputRow[name]] = $currentInputRow[attend];

    // read the next input row - may be current day, new day or no more
    $currentInputRow = mysql_fetch_array($query);
  }
  // end of day on the input for whatever reason...

  /* we now have:
   *   1) Current Date
   *
   *   2) an array of members for ALL the roles on that day.
   *
   *   We need to output it to another array ($outputDates) where we can print it out
   *   by scanning the array line by line later.
   *
   *   I will 'pivot' the array and produce an output array we can scan sequentially later.
   */

   // to ensure that we are updating the correct $outputDates row i will use a subscript
   $currentOutputRowIdx = 0;

   // first add the current date to the output...
   $outputDates[$currentOutputRowIdx][] = $currentDay;
   $currentOutputRowIdx++; // next output row

   // we need to drive off the '$allRoles' array to add the name data in the correct order
   foreach ($allRoles as $outRole) {
     $outputDates[$currentOutputRowIdx][] = $theDayRoles[$outRole];
     $currentOutputRowIdx++; // next output row
   }

} // end of all the input data


/*
 * Now we just need to print the outputDates array one row at a time...
 */

// need the roles as the first column...
// so we need an index for which one we are currently printing

$currentRoleIdx = -1; // increment each time but allow for the first row being the title 'Roles'

echo '<table>';
foreach ($outputDates as $oneOutputRow) {

  echo '<tr>';

  // this is the first column...
  if ($currentRoleIdx < 0) {
    echo '<td>'. 'NAME' .'</td>';
  }
  else {
    echo '<td>'. $allRoles[$currentRoleIdx] .'</td>';
  }

  // now output the day info
  foreach($oneOutputRow as $column) {
    echo '<td>'. $column .'</td>';
  }
  echo '</tr>';
  $currentRoleIdx++; // next output name to show...

}
echo '</table>';

?>
</body>
</html>

Thanks Barand sir for quick reply.. Sir here is my code for same problem but i have a issue please guide me little in line no: 66 how can i take dynamic array.. Thank you in advance please help me out

Edited March 11, 2016 by Ritika

Barand · March 11, 2016

Reply #13 above demonstrates the method. Dynamic values would come from your db data.

Ritika · March 13, 2016

will you please give me example please... it will be grate help for me

Barand · March 13, 2016

That earlier post was an example. Here's another

http://forums.phpfreaks.com/topic/262473-pivot-table-like-output-indefinite-rows-and-columns-from-flat-data/?do=findComment&comment=1345109

Or do you mean an example which uses your data so that I write your code for you?

ttcc · August 16, 2016

Hi Sen,

the code is really fine, but there is something that I can't understand very well. Exactly the variable $curname. How it works? Can you explain me please?

".....

$curname=' ';
$tdata = ' ';
while (list($d, $sn, $s) = $res->fetch_row()) {

    if ($curname != $sn) {
        if ($curname) {
            $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n";
        }
        $rowdata = $emptyRow;
        $curname = $sn;
    }

...."

:confused:

Barand · August 16, 2016

Hi Newbie,

You are also incapable of working out that my name in my posts is in exactly the same place as your name is in your posts. (Strange, but true.)

$curname stores the current name. I then test the value of the new name against to see if it has changed, and then store the new value in $curname.

ttcc · August 16, 2016

Hi Newbie,

You are also incapable of working out that my name in my posts is in exactly the same place as your name is in your posts. (Strange, but true.)

$curname stores the current name. I then test the value of the new name against to see if it has changed, and then store the new value in $curname.

sorry, Barand, Mistakes are always behind the corner

anyway, thank you for your quick answer.

I have to say I m not a very expert with php,

the code starts with the variable empty. I don't understand when this variable stores the value $sn.

I tried with an echo after the while() loop and the variable has already the value of $sn. How does it happen, and when?

"....

while (list($d, $sn, $s) = $res->fetch_row())

{

echo $curname; //output of all the names!!! " how???"

if ($curname != $sn) {

..."

thank you

Sign In

Pivot Table or Cross Tab in PHP using MYSQL for attendance

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information