Monthly Archives: January 2010
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 github
Building a simple HTTP client with PHP. A REST client
REST webservices are cool. Nowadays there are a lot of APIS with REST and in combination with JSONP is really easy to build applications. Actually I am playing with CouchDB. It has a nice RESTfull API and I want to use it with PHP. There are several PHP libraries, even a nice PHP extension to work with CouchDB. I like Zend Framework’s REST client but as exercise I will develop a HTTP client. My idea is to create a simple class that allows me to perform GET, POST and DELETE requests to a remote server.
That’s the way I want to use my class:
echo Http::connect('localhost', 8082) ->doGet('tests/httpclient/dummy.php', array('a' => "a a a"));
First I want to do a lazy connection to server. That’s because I want to create the real connection only when I create the request. So I create a factory function that only saves the configuration of the client:
private $_host = null; private $_port = null; private $_user = null; private $_pass = null; /** * Factory of the class. Lazy connect * * @param string $host * @param integer $port * @param string $user * @param string $pass * @return Http */ static public function connect($host, $port, $user=null, $pass=null) { return new self($host, $port, $user, $pass); }
And now one public function for each action:
const POST = 'POST'; const GET = 'GET'; const DELETE = 'DELETE'; /** * POST request * * @param string $url * @param array $params * @return string */ public function doPost($url, $params=array()) { return $this->_exec(self::POST, $this->_url($url), $params); } /** * GET Request * * @param string $url * @param array $params * @return string */ public function doGet($url, $params=array()) { return $this->_exec(self::GET, $this->_url($url), $params); } /** * DELETE Request * * @param string $url * @param array $params * @return string */ public function doDelete($url, $params=array()) { return $this->_exec(self::DELETE, $this->_url($url), $params); }
And one extra function to set the headers of the request
private $_headers = array(); /** * setHeaders * * @param array $headers * @return Http */ public function setHeaders($headers) { $this->_headers = $headers; return $this; }
I use a settler function and chainable (returns the instance of the class) because if I need to use some custom headers I want to use the following interface:
echo Http::connect('localhost', 8082) ->setHeaders($myCustomHeaders) ->doGet('tests/httpclient/dummy.php', array('a' => "a a a"));
And finally the main function. The function that perform the real request. There are several ways to do that in PHP. For this example I will use CURL’s functions. You must take care about it because CURL extension is not always available in PHP installations. So please check your phpinfo(). If CURL is not available and if you are not allowed to install it you must chose another alternatives.
const HTTP_OK = 200; const HTTP_CREATED = 201; const HTTP_ACEPTED = 202; /** * Performing the real request * * @param string $type * @param string $url * @param array $params * @return string */ private function _exec($type, $url, $params = array()) { $headers = $this->_headers; $s = curl_init(); if(!is_null($this->_user)){ curl_setopt($s, CURLOPT_USERPWD, $this->_user.':'.$this->_pass); } switch ($type) { case self::DELETE: curl_setopt($s, CURLOPT_URL, $url . '?' . http_build_query($params)); curl_setopt($s, CURLOPT_CUSTOMREQUEST, self::DELETE); break; case self::POST: curl_setopt($s, CURLOPT_URL, $url); curl_setopt($s, CURLOPT_POST, true); curl_setopt($s, CURLOPT_POSTFIELDS, $params); break; case self::GET: curl_setopt($s, CURLOPT_URL, $url . '?' . http_build_query($params)); break; } curl_setopt($s, CURLOPT_RETURNTRANSFER, true); curl_setopt($s, CURLOPT_HTTPHEADER, $headers); $_out = curl_exec($s); $status = curl_getinfo($s, CURLINFO_HTTP_CODE); curl_close($s); switch ($status) { case self::HTTP_OK: case self::HTTP_CREATED: case self::HTTP_ACEPTED: $out = $_out; break; default: throw new Http_Exception("http error: {$status}", $status); } return $out; }
I’ve have also created a Http_Exception Exception class:
class Http_Exception extends Exception{ const NOT_MODIFIED = 304; const BAD_REQUEST = 400; const NOT_FOUND = 404; const NOT_ALOWED = 405; const CONFLICT = 409; const PRECONDITION_FAILED = 412; const INTERNAL_ERROR = 500; }
Http throws Http_Exception when something wrong happens so I can play with it to build my application logic.
For example:
try { echo Http::connect('localhost', 8082) ->doGet('tests/couchdb/a.php', array('a' => "a a a")); } catch (Http_Exception $e) { switch ($e) { case Http_Exception::INTERNAL_ERROR: // do something break; } }
And that’s it. There is the full code available on bitbucket.
UPDATED.
See a variation of this class in the next article: Building a REST client with asynchronous calls using PHP and curl