Introduction

Installation/Requirements

Download the latest MC_Google_Visualization and extract the contents of the lib folder to your PHP include_path. You should now find a top-level MC directory which contains the parser and visualization compontents, and you're done!

System Requirements

PHP 5
JSON extension or Zend_Json from the Zend Framework
PDO (for automatic request handling and pivots)
magic_quotes_gpc must be turned off

Handling Visualization Query Requests

Simple usage follows this pattern:

<?php
require_once 'MC/Google/Visualization.php';
$db = new PDO('sqlite:example.db');
$vis = new MC_Google_Visualization($db, 'sqlite');

$vis->addEntity('some_table', array(
    'fields' => array(
        'col1' => array('field' => 'col1', 'type' => 'text'),
        'col2' => array('field' => 'col2', 'type' => 'number')
    )
));

$vis->handleRequest();
?>

If you take the above code and save it to /vis.php on your web server, you can start making visualization requests against your database like so:

<html>
  <head>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["piechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        //Tell Google Visualization where your script is
        var query = new google.visualization.Query('/vis.php');
        query.setQuery('select col1, col2 from some_table order by col2 desc');
        query.send(function(result) {
          if(result.isError()) {
            alert(result.getDetailedMessage());
          } else {
            var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
            chart.draw(result.getDataTable(), {width: 400, height: 240});
          }
        });
      }
    </script>
  </head>

  <body>
    <div id="chart_div"></div>
  </body>
</html>

Mapping Your Database

Before you can start querying against your database, you must tell MC_Google_Visualization which fields and tables will be allowed be queried against. Any fields or tables in your database will not be exposed to visualization queries and will throw an error if attempted.

Entities

You make your database tables and fields available for visualization queries by defining entities. An entity is one or more joined database tables that expose a set of fields that can be queried against. Fields can be simple database fields, the result of SQL functions, or use PHP callback functions to calculate the value. Entities are defined by call addEntity($name, $spec) with the name of the entity (to be used in the "from" clause of queries) and a spec array defining the table, fields, and joins that make up the entity.

Entity Spec Array

Key	Type	Description
table	string	optional - The database table to map the entity against. If this is not provided, the entity name will be used by default
fields	array	Array of $field_name => $field_spec pairs that define which fields will be exposed for this entity.
joins	array	Array of $join_name => $join_sql pairs that define the join statements that some entity fields might require. The fields can define which join they require by providing a "join" option that matches a "join_name" provided here.
where	string	optional - an extra condition that will automatically be added to the "where" clause whenever this entity is used.

Each field defined in the entity also has a spec array that defines the mapping for each field.

Field Spec Array

Key	Type	Description
field	string	The SQL expression that this entity field maps to. If this is not provided, a callback must be given.
callback	callback	A PHP callback that is used to generate the value instead of pulling straight from the database. Callback fields can also include the fields and extra keys.
type	string	The data type for the field. This must be one of "text", "number", "boolean", "date", "datetime", "timestamp", or "time"
join	string	If this field depends on fields in a joined table, this must be set to the key of the join to include in the entities "joins" list
fields	array	Callback fields can include this option to give a set of entity fields that the callback depends on to generate its data. Use this if the callback field is transforming data in the dataset.
extra	array	Callback fields can include this option to provide extra parameters that will be passed along to the callback.
sort_field	string	Use this option to delegate sorting to another entity field. This option can be used to make call back fields sortable.

More Information

Comment by hrovira, Sep 08, 2008

I see a problem with being able to write SQL queries directly from the javascript. How do you protect against malicious behavior?

Someone could easily make a request using your API to "delete from some_table", right?

Comment by c...@mailchimp.com, Sep 08, 2008

You don't actually write SQL directly. The queries you write are in the Google Visualization Query Language, which is a variant on the syntax for SQL SELECT queries. So no modifying or destructive queries are allowed.

On top of that, you limit the fields that are accessible via javascript through defining entities. If you have any fields that should not be user-visible, simply leave them off of the entity definition. That will make them unavailable to the javascript API.

Comment by paulrene, Apr 01, 2009

@hrovira: who allows a db user with insert, update and delete rights to be used in such a script anyway??

Comment by a...@geepok.com, May 19, 2009

where do I have to put the "Group by" elements ?

Comment by radioactivewraith, Oct 29, 2009

Your API documentation appears to be down.

Comment by juliencollinet, Dec 09, 2009

I am trying to use the function toDate(dateTime myDateTime) in a "select" clause. Could you give me an example of use ? Thank you