How do I create a Crosstab table with php mysql

Question

I have the following recordset:

Date     |Role      |Name
=============================
01/02/14 |Musician  |Bob
01/02/14 |Leader    |Jerry
01/02/14 |Singer    |Carol
08/02/14 |Musician  |Charles
08/02/14 |Leader    |Baz
08/02/14 |Singer    |Norman

and I want the data to be displayed like a rota/roster format. EG.

Role     |01/02/14  |08/02/14
===============================
Musician |Bob       |Charles
Leader   |Jerry     |Baz
Singer   |Carol     |Norman

Ideally, I'd like it to be done in php without changing the MySQL query.

This is what I've got so far but it's not quite working.

$temprole='norole';   

$newkey=0;


echo "<table><tr>";
foreach ($result as $key => $val) {
echo "<td>" . $val['date'] . "</td>";

if ($temprole==$val['role_name']){ //is the same role?

 } else {
   //If the role name is different, print the role column
 echo $val['role_name'] . "</br>";
  }
        $temprole = $val['role_name'];
  }

 echo "</tr></table>";



echo "<hr>";

You should be able to do it with loops. Even though you don't want to change the SQL, you should have the result set sorted by Role (the row) then the Date (the column). Then loop: Create a new role every change in Role and put in the appropriate HTML table column for each date. — AgRizzo, Mar 05 '14 at 14:19
possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Strawberry, Mar 05 '14 at 14:22
"I'd like it to be done..." good idea. Just use a simple loop. — Strawberry, Mar 05 '14 at 14:23
Could someone give me a more clear example on how to do this with loops in php? thanks. — Jeremy Walmsley, Mar 05 '14 at 14:57
A general solution todo what you require is not as trivial as it first appears. For example what if some of the roles are missing for a particular day. Also what if you want the roles printed in some different order. I have thought about it and will try and create some code to deal with the above situation and still be easy to change. — Ryan Vincent, Mar 06 '14 at 08:21

Ryan Vincent · Accepted Answer · 2014-03-06T22:29:45.363

Goodness, that was fun... :-/

This is tested code that does as required. There are lots of comments. Feel free to remove them to see the code more clearly. Whatever...

You should be able to change the $allRoles array to get the roles to print in a different order. I have tried it and it works fine.

It runs on PHP 5.3.18 on windows XP (XAMPP).

Added some css to make the table clearer.

Changed the code to read the data from a 'mysqli' query rather than an array

see the lines marked '!important' to ensure it works correctly.

sample output:

Roles       01/02/14        05/02/14        08/02/14
musician    Bob             Donald          Charles
leader      Jerry           --              Baz
singer      Carol           Freddy          Norman

code:

<!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

/*
 * Some test data base on:
 * Date     |Role      |Name
   =============================
   01/02/14 |Musician  |Bob
   01/02/14 |Leader    |Jerry
   01/02/14 |Singer    |Carol
   08/02/14 |Musician  |Charles
   08/02/14 |Leader    |Baz
   08/02/14 |Singer    |Norman
 *
 */

 /* sample output:
  *
  * Role     |01/02/14  |08/02/14
    ===============================
    Musician |Bob       |Charles
    Leader   |Jerry     |Baz
    Singer   |Carol     |Norman
  */

$db = mysqli_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 = mysqli_query($db, "SELECT service_date, role, member FROM role_by_date ORDER BY service_date ASC, role 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', 'service_date'); // !important
define('ROLE',     'role');         // !imortant
define('MEMBER',   'member');       // !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('musician', 'leader', 'singer'); // !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 = mysqli_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 $role) {
    $theDayRoles[$role] = '--';
  }

  // 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 MEMBER
    $theDayRoles[$currentInputRow[ROLE]] = $currentInputRow[MEMBER];

    // read the next input row - may be current day, new day or no more
    $currentInputRow = mysqli_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 role 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>'. 'Roles' .'</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 Role to show...

}
echo '</table>';

?>
</body>
</html>

Thank you for this, Its a great help. I've tried modifying your code to get the dataset from my database but I'm getting "Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/vhosts/cfmworship.org.uk/gem2.php on line 125". 125 corrisponds to ' // read the next input row - may be new day or no more $currentRoleRow = next($testData);' Any ideas why this is happening? — Jeremy Walmsley, Mar 06 '14 at 12:47
You can view my complete code here: http://www.cfmworship.org.uk/rota_code.txt any suggestions would be really helpful. thanks. — Jeremy Walmsley, Mar 06 '14 at 12:58
You need to replace all the reads from the 'testData' array with 'fetching the next row' from the testData query. It should then work as expected. so replace 'current(testData) and next(testData) with fetches. — Ryan Vincent, Mar 06 '14 at 13:03
Thank you for the code, it is looking a little bit more what I am aiming towards. However, the results don't appear to be displaying correctly in the grid. see http://www.cfmworship.org.uk/gam2.php as an example. Not sure what is happening here. Any help would be really appreciated. many thanks! — Jeremy Walmsley, Mar 06 '14 at 20:00
The results appear to be staggered across the dates, when really should all be lined up. ie. three people per date. — Jeremy Walmsley, Mar 06 '14 at 20:05
Please see the posted answer. I have tidied it up and explained things a little better. I use the comment '!important' in the code to indicate things that need to be edited or checked. My issue for not explaining things more clearly earlier. — Ryan Vincent, Mar 06 '14 at 23:47

score 1 · Answer 2 · edited Aug 03 '20 at 08:37

    <?php
        echo '<div align=left>';
        //initialize three arrays to hold the values
        $x_role = array();
        $y_date = array();
        $val_name = array();
        
        //store values from the database into three separate arrays NB theRole, 
        // theDate  and theName are the column names from the database
        foreach($data as $recordset)
        {
           $x_role[] =  $recordset->theRole;
           $y_date[] =  $recordset->theDate;    
           $val_name[$recordset->theRole][$recordset->theDate] = $recordset->theName;   
        }
        
        //get unique values for row and column and sort them if necessary
        $unique_x_role = array_unique($x_role);
        //asort($unique_x_role);    
        
        $unique_y_date = array_unique($y_date);
        //asort($unique_y_date);    
        
        // prints table - OUTPUT
        echo '<p>';
        echo '<hr size=10 color=#6f9171 width=100% align=left>';
        echo '<p>';
        echo '<div align=left>';
        
        echo '<table border=3 >';
        
        echo '<tr>';
        echo '<td>ROLE</td>';//prints 'ROLE" in upper left corner of table  
        foreach($unique_y_date as $theDate)
        {
           echo '<td>'.$theDate.'</td>';//prints column headings    
        }
        echo '</tr>';//closes column headings   
        
        foreach($unique_x_role as $theRole)
        {
            echo '<tr>';
            echo '<td>'.$theRole.'</td>';   //prints row title  
            foreach($unique_y_date as $theDate)
            {
                if(isset($val_name[$theRole][$theDate]))// checks if value exists
                {
                   $v =     $val_name[$theRole][$theDate];
                   echo '<td>'.$v.'</td>';  //prints name because it exists
                }
                else
                {
                    echo '<td> - </td>';// prints a dash if  no name exists
                }
            }//foreach($y_date as $theDate)
            echo '</tr>';
        }//foreach($unique_x_role as $theRole)
        
        echo '</table>';
        echo '</div>';
        
 ?>

score 0 · Answer 3 · answered Aug 03 '20 at 10:00

...this WAS fun. I decided to replicate the desired output as raw text rather than html as a personal challenge.

Essentially, the data is formed into reference arrays, then imploded or iterated print the desired crosstab layout. The $columnWidth variable allows the whole table to be easily re-sized. str_pad() is used for center alignment. The null coalescing operator (??) is used to fallback to - when a respective value does not exist.

Code: (Demo)

//It is assumed that the sql is perfectly capable of sorting by date ASC, role ASC, name ASC
$resultSet = [
    ['date' => '01/02/14', 'role' => 'Leader', 'name' => 'Jerry'],
    ['date' => '01/02/14', 'role' => 'Musician', 'name' => 'Bob'],
    ['date' => '01/02/14', 'role' => 'Singer', 'name' => 'Carol'],
    ['date' => '08/02/14', 'role' => 'Leader', 'name' => 'Baz'],
    ['date' => '08/02/14', 'role' => 'Leader', 'name' => 'Gaz'],
    ['date' => '08/02/14', 'role' => 'Leader', 'name' => 'Haz'],
    ['date' => '08/02/14', 'role' => 'Musician', 'name' => 'Charles'],
    ['date' => '08/02/14', 'role' => 'Singer', 'name' => 'Norman'],
    ['date' => '15/02/14', 'role' => 'Astronaut', 'name' => 'Neil'],
];

$columnWidth = 20;

foreach ($resultSet as ['date' => $date, 'role' => $role, 'name' => $name]) {
    $nested[$date][$role][] = $name;
    $dates[$date] = str_pad($date, $columnWidth, " ", STR_PAD_BOTH);
    $roles[$role] = str_pad($role, $columnWidth, " ", STR_PAD_BOTH);
}
$totalColumns = count($dates) + 1;

// HEADINGS
printf(
    implode("|", array_fill(0, $totalColumns, '%s')) . "\n",
    str_pad('Roles', $columnWidth, " ", STR_PAD_BOTH),
    ...array_values($dates)
);

// SEPARATOR
echo implode("|", array_fill(0, $totalColumns, str_repeat('=', $columnWidth)));

// DATA
foreach ($roles as $role => $paddedRole) {
    echo "\n$paddedRole";
    foreach ($nested as $date => $roleGroup) {
        echo '|' . str_pad(implode(', ', $nested[$date][$role] ?? ['-']), $columnWidth, " ", STR_PAD_BOTH);
    }
}

Output:

       Roles        |      01/02/14      |      08/02/14      |      15/02/14      
====================|====================|====================|====================
       Leader       |       Jerry        |   Baz, Gaz, Haz    |         -          
      Musician      |        Bob         |      Charles       |         -          
       Singer       |       Carol        |       Norman       |         -          
     Astronaut      |         -          |         -          |        Neil

How do I create a Crosstab table with php mysql

3 Answers3

Linked