Pivot tables in PHP


In my work as developer I normally need to transform data from one format to another. Typically our input data is a database and we need to show the database data into a report, datagrid or something similar. It’s very typical to use pivot tables. It’s not very dificult to handle pivot tables by hand but the work is always the same: groups by, totals, subtotals, totals per row …. Now I want to write a class to pivot tables in PHP with the most common requirements (at least for me). I know we can create pivot tables with SQL. Group by and some other database specific commands like oracle’s GROUP BY ROLLUP/CUBE can do the work, but I like clean SQL querys. The business logic must be in PHP and SQL must be as clean as we can.

Let’s show you an example. We have the following recordset from a SQL:

$recordset = array(
    array('host' => 1, 'country' => 'fr', 'year' => 2010,
        'month' => 1, 'clicks' => 123, 'users' => 4),
    array('host' => 1, 'country' => 'fr', 'year' => 2010,
        'month' => 2, 'clicks' => 134, 'users' => 5),
    array('host' => 1, 'country' => 'fr', 'year' => 2010,
        'month' => 3, 'clicks' => 341, 'users' => 2),
    array('host' => 1, 'country' => 'es', 'year' => 2010,
        'month' => 1, 'clicks' => 113, 'users' => 4),
    array('host' => 1, 'country' => 'es', 'year' => 2010,
        'month' => 2, 'clicks' => 234, 'users' => 5),
    array('host' => 1, 'country' => 'es', 'year' => 2010,
        'month' => 3, 'clicks' => 421, 'users' => 2),
    array('host' => 1, 'country' => 'es', 'year' => 2010,
        'month' => 4, 'clicks' => 22,  'users' => 3),
    array('host' => 2, 'country' => 'es', 'year' => 2010,
        'month' => 1, 'clicks' => 111, 'users' => 2),
    array('host' => 2, 'country' => 'es', 'year' => 2010,
        'month' => 2, 'clicks' => 2,   'users' => 4),
    array('host' => 3, 'country' => 'es', 'year' => 2010,
        'month' => 3, 'clicks' => 34,  'users' => 2),
    array('host' => 3, 'country' => 'es', 'year' => 2010,
        'month' => 4, 'clicks' => 1,   'users' => 1),
);
host country year month clicks users
1 fr 2010 1 123 4
1 fr 2010 2 134 5
1 fr 2010 3 341 2
1 es 2010 1 113 4
1 es 2010 2 234 5
1 es 2010 3 421 2
1 es 2010 4 22 3
2 es 2010 1 111 2
2 es 2010 2 2 4
3 es 2010 3 34 2
3 es 2010 4 1 1

And I need the information grouped by host and show it in a table with: each row one host and each column the sum of clicks and users per month

The interface I have created is the following one:

$data = Pivot::factory($recordset)
    ->pivotOn(array('host'))
    ->addColumn(array('year', 'month'), array('users', 'clicks',))
    ->fetch();

Let’s explain the interface:

Loads the original recorset into the class:

Pivot::factory($recordset)

I want to pivot the recordset on the field ‘host’

->pivotOn(array('host'))

The columns are grouped by year and month and each group will show the sum of users and clicks

->addColumn(array('year', 'month'), array('users', 'clicks',))

And finally I make the transformation:

->fetch();

Basically that’s it. I also has made some other helpers to:
Add totals per line
Add total per pivoted colum
Add total for all table

->fullTotal()
->pivotTotal()
->lineTotal()

In this class I allow to pivot tables with one, two or three fields. And also I allow to use calculated columns.
For example imagine you need to show users, clicks and the average of clicks per user. It’s easy clicks/users but you must take care in the calculated column because you can’t sum it. You must calculate the calculated column over the sum of clicks and user. Because of that I define calculated functions. This feature only works with PHP5.3 because it uses closures.


$averageCbk = function($reg)
{
    return round($reg['clicks']/$reg['users'],2);
};

$data = Pivot::factory($recordset)
    ->pivotOn(array('host', 'country'))
    ->addColumn(array('year', 'month'), array('users', 'clicks', Pivot::callback('average', $averageCbk)))
    ->lineTotal()
    ->pivotTotal()
    ->fullTotal()
    ->typeMark()
    ->fetch();

And now different usage examples:

pivot on ‘host’

$data = Pivot::factory($recordset)
    ->pivotOn(array('host'))
    ->addColumn(array('year', 'month'), array('users', 'clicks',))
    ->fetch();
_id host 2010_1_users 2010_1_clicks 2010_2_users 2010_2_clicks 2010_3_users 2010_3_clicks 2010_4_users 2010_4_clicks
1 1 8 236 10 368 4 762 3 22
2 2 2 111 4 2
3 3 2 34 1 1

pivot on ‘host’ with totals

$data = Pivot::factory($recordset)
    ->pivotOn(array('host'))
    ->addColumn(array('year', 'month'), array('users', 'clicks',))
    ->fullTotal()
    ->lineTotal()
    ->fetch();
_id host 2010_1_users 2010_1_clicks 2010_2_users 2010_2_clicks 2010_3_users 2010_3_clicks 2010_4_users 2010_4_clicks TOT_users TOT_clicks
1 1 8 236 10 368 4 762 3 22 25 1388
2 2 2 111 4 2 6 113
3 3 2 34 1 1 3 35
4 TOT 10 347 14 370 6 796 4 23 34 1536

pivot on ‘host’ and ‘country’

$data = Pivot::factory($recordset)
    ->pivotOn(array('host', 'country'))
    ->addColumn(array('year', 'month'), array('users', 'clicks',))
    ->fullTotal()
    ->pivotTotal()
    ->lineTotal()
    ->fetch();
_id host country 2010_1_users 2010_1_clicks 2010_2_users 2010_2_clicks 2010_3_users 2010_3_clicks 2010_4_users 2010_4_clicks TOT_users TOT_clicks
1 1 fr 4 123 5 134 2 341 11 598
2 1 es 4 113 5 234 2 421 3 22 14 790
3 TOT(host) 8 236 10 368 4 762 3 22 25 1388
4 2 es 2 111 4 2 6 113
5 TOT(host) 2 111 4 2 0 0 0 0 6 113
6 3 es 2 34 1 1 3 35
7 TOT(host) 0 0 0 0 2 34 1 1 3 35
8 TOT 10 347 14 370 6 796 4 23 34 1536

pivot on ‘host’ and ‘country’ with calculated columms

$averageCbk = function($reg)
{
    return round($reg['clicks']/$reg['users'],2);
};

$data = Pivot::factory($recordset)
    ->pivotOn(array('host', 'country'))
    ->addColumn(array('year', 'month'), array('users', 'clicks', Pivot::callback('average', $averageCbk)))
    ->lineTotal()
    ->pivotTotal()
    ->fullTotal()
    ->typeMark()
    ->fetch();
_id type host country 2010_1_users 2010_1_clicks 2010_1_average 2010_2_users 2010_2_clicks 2010_2_average 2010_3_users 2010_3_clicks 2010_3_average 2010_4_users 2010_4_clicks 2010_4_average TOT_users TOT_clicks TOT_average
1 0 1 fr 4 123 30.75 5 134 26.8 2 341 170.5 0 11 598 54.36
2 0 1 es 4 113 28.25 5 234 46.8 2 421 210.5 3 22 7.33 14 790 56.43
3 1 TOT(host) 8 236 29.5 10 368 36.8 4 762 190.5 3 22 7.33 25 1388 264.13
4 0 2 es 2 111 55.5 4 2 0.5 0 0 6 113 18.83
5 1 TOT(host) 2 111 55.5 4 2 0.5 0 0 0 0 0 0 6 113 56
6 0 3 es 0 0 2 34 17 1 1 1 3 35 11.67
7 1 TOT(host) 0 0 0 0 0 0 2 34 17 1 1 1 3 35 18
8 3 TOT 10 347 34.7 14 370 26.43 6 796 132.67 4 23 5.75 34 1536 45.18

UPDATED

As Marcin said in a comment we can add the count of grouped columns. This functionality was not available on the first release of Pivot class. I think it can be useful so I’ve developed it
Some examples:

pivot on ‘host’ and ‘country’ with group count

$data = Pivot::factory($recordset)
    ->pivotOn(array('host', 'country'))
    ->addColumn(array('year', 'month'), array('users', 'clicks', Pivot::count('count')))
    ->fullTotal()
    ->pivotTotal()
    ->lineTotal()
    ->fetch();
_id host country 2010_1_users 2010_1_clicks 2010_1_count 2010_2_users 2010_2_clicks 2010_2_count 2010_3_users 2010_3_clicks 2010_3_count 2010_4_users 2010_4_clicks 2010_4_count TOT_users TOT_clicks TOT_count
1 1 fr 4 123 1 5 134 1 2 341 1 11 598 3
2 1 es 4 113 1 5 234 1 2 421 1 3 22 1 14 790 4
3 TOT(host) 8 236 2 10 368 2 4 762 2 3 22 1 25 1388 7
4 2 es 2 111 1 4 2 1 6 113 2
5 TOT(host) 2 111 1 4 2 1 0 0 0 0 0 0 6 113 2
6 3 es 2 34 1 1 1 1 3 35 2
7 TOT(host) 0 0 0 0 0 0 2 34 1 1 1 1 3 35 2
8 TOT 10 347 3 14 370 3 6 796 3 4 23 2 34 1536 11

pivot on ‘country’ with group count

$data = Pivot::factory($recordset)
    ->pivotOn(array('host'))
    ->addColumn(array('country'), array('year', Pivot::count('count')))
    ->lineTotal()
    ->fullTotal()
    ->fetch();
_id host fr__year fr__count es__year es__count TOT_year TOT_count
1 1 6030 3 8040 4 14070 7
2 2 4020 2 4020 2
3 3 4020 2 4020 2
4 TOT 6030 3 16080 8 22110 11

The source code of the class is available in google code

About these ads

About Gonzalo Ayuso

Web Architect specialized in Open Source technologies. PHP, Python, JQuery, Dojo, PostgreSQL, CouchDB and node.js but always learning.

Posted on January 24, 2010, in php, Technology. Bookmark the permalink. 49 Comments.

  1. It was interesting to read this article and I hope to read a new article about this subject in your site in the near time.

  2. Very interesting class.
    Very useful to make a customized BI reports.

    Greetings!

  3. very handy class thanks for sharing

    Alex

  4. Hi,

    I can’t find the source code of the class Pivot.

    in your blog you said:
    The source code of the class is available in google code

    Can you please update the link.

    Thanks.

    • The source code is available on google code. ‘google code’ words are the link to the souce code. Here you have also a link to the class link.

  5. Thanks for the excellent code Gonzalo. Works like a charm.

    Ed

  6. Hi,

    Your class is very usefull, many thanks.
    However, I have a problem. Which is the version of PHP do you use ? Because I have an error with the function declaration :
    $averageCbk = function($reg)
    {
    return round($reg['clicks']/$reg['users'],2);
    };
    and also with func_get_args()…

    Is it the 5.3.0+ ?

    Thanks and sorry if my english is not perfect… ;-)

    • I reply to myself.
      My PHP version was 5.2. I upgrade it to 5.3.2 and it works like a charm. =)

      • PHP 5.2 is dead. Long life to PHP5.3 :)
        The script works with PHP 5.2 if you don’t use callback functions as you said (or at least worked in the beginning. It’s only tested with PHP5.3). I think it’s possible to do the same with PHP<5.3 with some tricks with 'eval' function. But I prefer to use new callback functions in PHP5.3

  7. Hi,

    First, sorry for my english…if is not perfect…

    In your example….if I write:
    ->addColumn(array(‘country’), array(‘year’))

    answer is:
    host=1, fr__year=6030, es__year=8040

    And I want to have:
    host=1, fr__year=3, es__year=4

    How to do this??

    Second question..
    When i write:
    ->addColumn(array(‘year’), array(‘country’))

    answer is:
    host=1, 2010__country=0

    And I want to have:
    host=1, 2010__country=3
    I mean how to count variable of text type

    Is it posible in this version of your pivot class??

    Many thanks
    marcin

    • Gonzalo Ayuso

      This functionality was not available but I have developed now. I named it ‘group counts’ and there is a new function Pivot::count(‘name’) that does what u want. Pivot::count counts the number of elements in a grout (not the sum). ‘name’ is the the name you want as column name. There is a new version of the code at google code

  8. Very good class.
    Thanks a lot for your work.

    Is there a way to define a new constant to allow with an extended class (to prevent to hack your class) to define an another separator between columns.
    In your example you have “2010_1_users” in table header with no break line. If there is a lot of column your html table become larger, too large for the general layout.

    Thanks

  9. Thank you! Very helpful!

  10. hi,

    i am new to php.
    how i use this code

  11. Hi,

    This class is very nice and very useful. I needed some help on one aspect. Is it reasonable/common to pivot on more than 3 columns (I am facing such a requirement now). If so, what would be the best way to modify the class?

    thanks again

    • Gonzalo Ayuso

      Each pivot level adds more complexity. I avoid levels>3 like the plague. Anyway if you really need it you need to add the level into: the switch of line 102 (the easy part) and the switch of line161 (the hard part). When I started with the Pivot.php class I wanted a n level class, but I couldn’t do it. Because of that I only created 3 levels

  12. Hi, I have put in the code and class as per your test to make sure i had got i right, but i get the following issues when i try and run.

    Undefined offset: 1 in pivot.php on line 108 Notice: Undefined offset: 2010 in pivot.php on line 108 Notice: Undefined offset: 1 in pivot.php on line 108 Notice: Undefined index: users in \pivot.php on line 108 Notice: Undefined index: clicks i etc. this shows the same error on several lines of pivot.php.

    Line 108 is : $tmp[$k0][$reg[$split]][$reg[$column]][$item] += $reg[$item];
    Using php 5.3.5

    Thanks
    Pete

    • Yes. I known it. That happens because the way I use to sum totals. In the first iteration $tmp[$k0][$reg[$split]][$reg[$column]][$item] is unset so Undefined index will appear. But += will work and the total will be calculed. It’s better to check if the variable exits but I need to use an extra line to check if it’s set. I put a mental note to correct this issue. It works but Warnings and Notice aren’t cool :)

      • Hi Gonzalo

        I turned off errors but i do not get any output on my webpage?

        Below is the code from my page:

        1, ‘country’ => ‘fr’, ‘year’ => 2010, ‘month’ => 1, ‘clicks’ => 123, ‘users’ => 4),
        array(‘host’ => 1, ‘country’ => ‘fr’, ‘year’ => 2010, ‘month’ => 2, ‘clicks’ => 134, ‘users’ => 5),
        array(‘host’ => 1, ‘country’ => ‘fr’, ‘year’ => 2010, ‘month’ => 3, ‘clicks’ => 341, ‘users’ => 2),
        array(‘host’ => 1, ‘country’ => ‘es’, ‘year’ => 2010, ‘month’ => 1, ‘clicks’ => 113, ‘users’ => 4),
        array(‘host’ => 1, ‘country’ => ‘es’, ‘year’ => 2010, ‘month’ => 2, ‘clicks’ => 234, ‘users’ => 5),
        array(‘host’ => 1, ‘country’ => ‘es’, ‘year’ => 2010, ‘month’ => 3, ‘clicks’ => 421, ‘users’ => 2),
        array(‘host’ => 1, ‘country’ => ‘es’, ‘year’ => 2010, ‘month’ => 4, ‘clicks’ => 22, ‘users’ => 3),
        array(‘host’ => 2, ‘country’ => ‘es’, ‘year’ => 2010, ‘month’ => 1, ‘clicks’ => 111, ‘users’ => 2),
        array(‘host’ => 2, ‘country’ => ‘es’, ‘year’ => 2010, ‘month’ => 2, ‘clicks’ => 2, ‘users’ => 4),
        array(‘host’ => 3, ‘country’ => ‘es’, ‘year’ => 2010, ‘month’ => 3, ‘clicks’ => 34, ‘users’ => 2),
        array(‘host’ => 3, ‘country’ => ‘es’, ‘year’ => 2010, ‘month’ => 4, ‘clicks’ => 1, ‘users’ => 1),
        );
        include(‘pivot.php’);
        $data = Pivot::factory($recordset)
        ->pivotOn(array(‘host’))
        ->addColumn(array(‘year’, ‘month’), array(‘users’, ‘clicks’,))
        ->fullTotal()
        ->lineTotal()
        ->fetch();
        ?>
        Please advise if i am doing anything wrong!

        Thanks

        Pete

      • Could you help with a check there? This is exactly what I need, but bit new to PHP.

  13. It would be better to do with javascript, to get best performance on server side

    • Depends on your needs. I use this solution to build custom xls files to send by email. Because of that I’ve done it with PHP. Js is a good exercise too.

  14. This helped a lot to prevent me from trying to figure out how to convert a C#.net version into PHP. Do you have the latest version available without all of undefined index notices/warnings? I’ve been trying to modify it myself however I seem to be adding to the problem. However, I was successful at removing the extra column parameters from the columns so it doesn’t display (i.e., 2010_1_users becomes Users).

    Thank you

    • “touché” This library shows a lot warnings. Those warnings are due not to initialize the arrays (it’s very confortable, but not a good practice) I need to refactor a little bit the library and it’s a problem when it haven’t got test coverage (my second fault). I tried to refactor it but it’s difficult to to do it without test (why I didn’t use TDD here :( ). Give me time. I will do it.

      By the other hand I’m not agree with you with the “extra” column parameters. I need to name 2010_1_users because it must be different than 2010_2_users. Probably when we use 18n to show the data we will convert to “Users”, but that part belong to the view and not to the domain controller.

  15. Thx for sharing. Nadim, from Mauritius.

  16. Hello,
    i tried to get the source from google code but i get all the code on one single line for both pivot.php and test.php files.
    I use PSPAD as editor.
    When i save the files and i run them, i get all sort of strange messages I am using php 5.3 running on windows and undled in WAMP.
    I would appreciate if someone can help me.
    Thanks.
    Eric.

  17. hello sir… is this runnable in windows??

  18. Excelente recurso. Gracias por compartirlo.

  19. Hi, just testing your class, it is great !

    Just I think the demo would make more sense if you pivotOn year/month like this

    Pivot::factory($recordset)
    ->pivotOn(array(‘year’, ‘month’))
    ->addColumn(array(‘host’, ‘country’), array(‘users’, ‘clicks’,))
    ->fetch();

    This way the table grows vertically over time, and can be easily scrolled,
    and not horizontally and taking more and more width.

    year month 1_fr_users 1_fr_clicks 1_es_users 1_es_clicks 2_es_users 2_es_clicks 3_es_users 3_es_clicks
    2010 1 4 123 4 113 2 111
    2010 2 5 134 5 234 4 2
    2010 3 2 341 2 421 2 34
    2010 4 3 22 1 1

    Just saying

  20. Kamil Chalhub

    It is a really useful class. Great Job.

  21. I am trying pivot method to implement in PHP but its giving some error.

    Table is clientdetails with 3 attributes (clientname,productname,quantity).

    <?php

    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("HR") or die(mysql_error());

    $sql=mysql_query("select clientname, 'Software' as s, 'Desktop' as d
    FROM (
    select clientname,productname,quantity
    FROM clientdetails)
    ps pivot (Sum(quantity) for productname in ('Software' ,'Desktop'))") or die("". mysql_error());

    Its showing error as

    "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pivot (Sum(quantity) for productname in ('Software' ,'Desktop'))' at line 5 ".

    Can someone help me in this?

    Thanks,

    Aditya Singh

    • Your problem isn’t with the library is with the SQL. Ensure that your sql works with your database (using an mysql client) before using it with php

      • Yeah I got it. Pivot clause doesn’t work with mysql. I got my results with sql server

      • Pivot class doesn’t work with databases, it works with arrays. You must create the array from database using the database sql driver.

  22. Hi Gonzalo,

    Thanks for a great piece of code, is there any way to instead of having counts to pivot text? Am really i want to pivot lets say 10 rows, Columns 1 is the SKU and 2 is and attribute name and column 3 is the attribute valve.

    Column 1 to be the row identifier, column 2 to be the pivot and column 3 to be the valve.

    Tim.

  23. Hello Gonzalo,

    First of all: Fantastic job!
    I’ve got a question: Is it possible to pivot only 2 rows, e.g.:

    ->pivotOn(array(‘year’, ‘month’, ‘host’, ‘users’))
    ->addColumn(array(‘country’), array(‘clicks’))

    If I do it in this way, it doesn’t work. Can you please tell me if it’s possible?
    Thanks in advance!

    Andy

  24. Hi ,
    I want to genrate a excel pivot table from data in mysql using php can you please help me with it .
    Thank you

    • This is out of the scope of this post. Maybe you need to have a look to https://phpexcel.codeplex.com/ for example

      • You should cut the middle man (PHP) out in this equation and connect directly to your database from Excel using an ODBC datasource. My blog is down, but had a very detailled article on this, sorry …

      • It depends. You need access to the database to create a ODBC connection. Sometimes the database server isn’t connected to Internet and it’s not possible (but you also can use remote data with iqy files). You can also create simple csv files with php and let Excel to open those files. IMHO the best approach is build native xlsx files with PHPExcel. You can create complex files and it’s well documented. The bad part is that PHPExcell it’s slow as hell and to create big and complex files requires CPU time

  25. Hi and thanks for the class.

    I am having trouble getting it to work. I am getting an error “Warning: Illegal string offset ‘theInterval2′”. This key is loaded into the line ->pivotOn(array(‘theInterval2′))
    and my array is the result of the mysql_fetch_assoc() function. I can print_r my array to the browser and I get the following:

    Array ( [adGivenName] => Tom [adSurName] => Smith [CountOfEnteredBy] => 4 [theInterval2] => 1 )

    What could I be doing wrong?

    Thanks!

  26. I got it working.

    I had to include the function simpleHtmlTable() in my code. I didn’t notice that before. Thanks it’s working great now. One question, is it possible to eliminate the _id column?

    Thanks!

  1. Pingback: uberVU - social comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 978 other followers

%d bloggers like this: