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

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