pivot html table using php

Question

I have this php code:

$query_production = "SELECT uploadedby as name, sum(points) as points,
date_format(uploaddate,'%Y-%m-%d') as date FROM imsexport 
WHERE uploaddate BETWEEN '2014-01-01 00:00:00' and '2014-01-20 23:59:59' 
GROUP BY uploadedby,date";
$result_production = mysql_query($query_production);
$row_production = mysql_fetch_assoc($result_production);

The HTML Table output is

  name        points      date
  John         147  2014-01-01
  Bob          79   2014-01-01
  Joe          156  2014-01-01
  Sue          116  2014-01-01
  John         117  2014-01-02
  Bob          186  2014-01-02
  Sue          74   2014-01-02
  Bob          233  2014-01-03
  John         159  2014-01-03
  Sue          162  2014-01-03
  Bob          162  2014-01-04
  Sue          38   2014-01-05

How can I pivot this table to look display like this using php? I've already done this using mysql but the code is too long.

Name |2014-01-01|2014-01-02|2014-01-03|2014-01-04|2014-01-05
Bob      79          186       233         162      0
Joe      156          0         0           0       0
John     147         117       159          0       0
Sue      116          74       162          0       38

If you add an ORDER BY name, date you will have the data in the right order and you can loop through it, building your table and adding zero values where no results exist and new rows where the name changes. — jeroen
– jeroen, Commented Jan 25, 2014 at 2:22
PHP is not well suited to do so. MySql has the perfect CASE statements that does the job. SELECT uploadedby as name, SUM(case when date='2014-01-01' then points else null end) as '2014-01-01', SUM(case when date='2014-01-02' then points else null end) as '2014-01-02', SUM(case when date='2014-01-03' then points else null end) as '2014-01-03', SUM(case when date='2014-01-04' then points else null end) as '2014-01-04', SUM(case when date='2014-01-05' then points else null end) as '2014-01-05' from imsexport group by 1 — digitai
– digitai, Commented Jan 25, 2014 at 2:25
@ datelligent I've already done that. Problem is there are more than 30 dates x 35 names and the table is dynamic. I was hoping it could be done via php using foreach loop. I just couldn't figure out how to only display distinct values for names and dates. — rain
– rain, Commented Jan 25, 2014 at 2:28
@datelligent: -1. actually, PHP is perfectly suited to transform a normal SQL result into a pivot table. Doing it in SQL is HIDEOUSLY ugly and gets UGLIER as you add more columns. Imaging having to expand your version into a system that has columns for a couple YEARS worth of dates... — Marc B
– Marc B, Commented Jan 25, 2014 at 3:08
Yes indeed, I've done it in PHP pushing each new dimension into and array and then conforming a new query. Well deserved -1. However what do you think of doing so in mysql with GROUP_CONCAT. I'm really interested in this way. — digitai
– digitai, Commented Jan 25, 2014 at 15:43

Jared Farrish · Accepted Answer · 2014-01-25 03:11:15Z

0

This will get you most of the way there; what I've left to you is how to get the left offset correct (or the date/points column alignment; hint, you'll need to keep the date with the name/points pair and know it's offset in $cols).

Obviously you're dealing with rows out of a database, so it'll be a little bit different.

See the codepad demo link below the code.

<?php

$data = "John 147 2014-01-01
Bob 79 2014-01-01
Joe 156 2014-01-01
Sue 116 2014-01-01
John 117 2014-01-02
Bob 186 2014-01-02
Sue 74 2014-01-02
Bob 233 2014-01-03
John 159 2014-01-03
Sue 162 2014-01-03
Bob 162 2014-01-04
Sue 38 2014-01-05";

$data = explode("\n", $data);

$cols = array();
$pivot = array();

while ($line = array_shift($data)) {
    list($name, $points, $date) = explode(' ', trim($line));

    if (!$pivot[$name]) $pivot[$name] = array();

    array_push($cols, $date);

    array_push($pivot[$name], array('date' => $date, 'points' => $points));
}

$cols = array_unique($cols);

print_r($pivot);

echo implode('|', $cols);

echo PHP_EOL;

foreach ($pivot as $name => $row) {
    while ($entry = array_shift($row)) {
        echo str_pad($name, 7, ' ') . str_pad($entry['points'], 3, ' ', STR_PAD_LEFT) . '|';
    }

    echo PHP_EOL;
}

?>

http://codepad.org/WqqpKwn3

answered Jan 25, 2014 at 3:11

Jared Farrish

49.4k17 gold badges100 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rain Over a year ago

how do I do this together with the mysql_query, $data here is static? displayed on an html table?

mickmackusa Over a year ago

@Jared this answer generates Warnings 3v4l.org/3eDZM

Collectives™ on Stack Overflow

pivot html table using php

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related