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 google code

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

Follow

Get every new post delivered to your Inbox.

Join 869 other followers