DESCRIPTION

SQoogL Charts is a simple and lightweight module that creates a Google chart from a SQL source. Insert a MySQL query into a node and it will generate a chart by calling the Google Charts Tools. (http://code.google.com/intl/en-EN/apis/chart/).

We are currently developing this module. There isn't any GUI yet. You just insert a PHP function that can be called from any content type.

The project was born due to limitations of Views particularly due to the fact that it was impossible to make calculations AND generate a graph.

REQUIREMENTS Activate PHP filter module from core. It allows PHP code to be evaluated within a node. This module has been tested only on Drupal 7.7 but should work on any Drupal 7 versions. Let us know if it doesn’t!

HOW IT WORKS

Activate the module and edit “sqoogl.config.php” in drupal/sites/all/modules/sqoogl_charts.

• SQOOGLE_HOST is the host address of your sql database, generally localhost.
• SQOOGLE_USER is the MySQL user you want to use. If you want to use your Drupal tables with this module, it is safer to create a new mySQL user with less privileges.
• SQOOGLE_PASSWORD is the password associated with the user.
• SQOOGLE_DATABASE is the name of the MySQL database you want to use.

Once this parameter file is complete, you can call the following functions inside a node to draw a chart :

• function sqoogl($query, $type, $table, $x, $ctitle, $width, $height, $filters, $dependants, $html_table) string $query is the query string written in SQL Language. Every select item must be followed by AS alias where alias is the alias name of the column. The first column is column number 0. It's important for other variables. You have to avoid NULL values in your query.

• string $type is the google graph type. For now you can choose pie (for pie chart), or table(for table).

• boolean $table (true/false), if $table is true and $type is different form table, it displays a table under the graph.

• string $x represents the abscissa and ordinate of the graph, it contains the number of the columns. example : if $x = "0,3", then the graph will display the first column based on the fourth.

• string $ctitle contains a title for the graph. It's not useful if you're using the same title as your content. If you don't want a title, set it to false.

• integer $width is the width of the graph if it's not a table (table fits automatically to the columns). Default is 600.

• integer $height is the height of the graph if it's not a table (table fits automatically to the rows). Default is 400.

• string $filters is a list of columns where you want to be able to filter with. There is two kinds : NumberRangeFilter and StringFilter, but the function choose the good filter for you. If you want to be able to filter with column 3 and 5, simply set $filter with the value '3,5'. If you don't want filters, set the value to false.

• string $dependants allow you to set dependencies between filters. Important : the column numbers set in dependencies have to be in filters, otherwise you'll have an error. If you want that filter of column " (town) be dependant of the filter of column 5 (country), then simply set the value of dependant to '5-3'. If you need other dependencies, then separate them with a comma ('5-3,3,2'). If you don't need dependencies, set the value to false.

• boolean $html_table allow you to display the data of your SQL query in a HTML table. This is default to false, but you can set it to true if you want to debug your SQL query or check the availability of Google Charts API.

About date values : We wanted to be able to filter with dates values. Since Google Chart API doesn’t provide any date format filter, we decided to transform the unix timestamp to an integer as soon as the column alias contain ‘date’. The integer is built as follows : YEARMONTHDAY.HOURMINUTE. Example with october 5th 2011 3:10pm => 20111005.1510.

RECOMMENDATIONS

We recommend you create a new content type ready for SQoogL Charts. This way, you could add some default text to your custom content in order to have only to replace some values to have a beautiful google graph. In order to avoid NULL values in your query, we recommend you to use the mySQL function IFNULL(column, ‘Value_if_null’).

EXAMPLE

Once you’re module is installed and configured with a drupal mySQL source(not mandatory, but we used it for the example), insert this code in a new content to test it :

<?php $query = "

SELECT n.nid AS nid, IFNULL(n.title,'title') AS title, IFNULL(n.created,0) AS date FROM node n ORDER BY date DESC LIMIT 10

";

$type='table';

$table = true;

$x = '1,2';

$ctitle = 'Ten last nodes';

$width = 600; $height= 400;

$filters = '1,2';

$dependants = false;

sqoogl($query, $type, $table, $x, $ctitle, $width, $height, $filters, $dependants);

?>