Iordan Iordanov Blog

In this article I would like to present a helper class that I use in my php project HomeBook. I am very happy using it and I would like to share it with You. Everybody is free to use and modify it.

I like “Google Chart Tools” and I use them often in my web applications. To achieve good performance of my work and readable code I often have to create helpers classes and methods  that automate request and page generation. This is I suppose a common practice for many developers.

google.visualization.DataTable

All  “Google Chart” are populated with data using a common JavaScript DataTable class. Having a common data structure makes it easy to switch between chart types. This class exposes methods for sorting, modifying, and filtering data. There is a very good description of this class in Google Visualization API Reference.

GoogleVisualizationDataTable

This is the helper class that I use in my php project HomeBook to create and populate a google.visualization.DataTable using php. It provides a JSON string of the class instance, that can be used in all “Google Chart Tools”. The source code of the class can be found in the resource link at the end of this article. It is very simple and there is no need of many explanation. If you have questions, I will be happy to respond to them in the comments. Here I would like to present a sample usage af this class.

Example usage of GoogleVisualizationDataTable.class.php

Define a class that extend GoogleVisualizationDataTable (in the example GVDTUsers class).

In the class constructor setup:

• columns
• SQL query
• query parameters

class GVDTUsers extends GoogleVisualizationDataTable {

    public function __construct($user_bank_id) {

        $this->cols = array();

        $this->cols[] = new GVColumn("id", "ID", GVColumnType::STRING);
        $this->cols[] = new GVColumn("name", "Name", GVColumnType::STRING);
        $this->cols[] = new GVColumn("birthday", "Birthday", GVColumnType::DATE);
        $this->cols[] = new GVColumn("is_admin", "Admin", GVColumnType::BOOL);
        $this->cols[] = new GVColumn("loan", "Loan", GVColumnType::NUMBER);

        $this->setSQL("SELECT 
                        id, 
                        name, 
                        ".GoogleVisualizationDataTable::GTDateSelectStr("birthday").", 
                        is_admin, 
                        loan
                    FROM user
                    WHERE loan>0 AND  user_bank_id=?
                    ORDER BY loan DESC"); 

        $this->setBoundParamsArr(array('i', $user_bank_id));

        $this->rows = array();
    }

}

In the page where you would like to use data create class instance width suitable parameters, call GetData() metod to retrieve data and finally call toJSON() method that returns a JSON representation of the DataTable that can be passed to the DataTable constructor. For example:

$dt = new GVDTUsers($current_bank->getId());
$dt->GetData();
$dataJson = $dt->toJSON();

<script type="text/javascript">
....
var userDataTableJS = new google.visualization.DataTable(< php echo $dataJson >);
var chart = new google.visualization.SteppedAreaChart(document.getElementById('chart_user_div'));
var chartOptionsPH = {...};
chart.draw(userDataTableJS, chartOptionsPH); 
....
</script>

Important! If you have date/datetime fileld, as in the example, 2 additional rows of code are needed to remove “ in JSON date field value.

$dataJson = preg_replace('/\"new/', 'new', $dataJson);
$dataJson = preg_replace('/\)\"/', ')', $dataJson);

That’s it!

Here is a promised source file GoogleVisualizationDataTable.class.php.zip.

Note: I am using a common class Connect that implements all required functionality for database connection and data retreeval. In my case using mysqli for MySQL database. The class is common and different similar type of implementations can be found on the web.