Dynamically chart some data stored in a database table

(The following documentation applies only to 7.x)

This document describes how to make a simple line chart using the Charts module and a table from a database.

The topics that are covered are:

• Requirements and installation
  • Compatibility issues with other programs
• Contents and format of the database table
• Creating the chart
  • Setting up Charts
  • Writing the PHP to display the chart

Background

The Charts module is a charting API and is set up to display charts using the following chart plugins: Google Charts, Open Flash Chart, and FusionCharts. This example uses Google Charts since, according to the Readme file for the Charts module, it is the easiest to use and does not require any additional downloads. Open Flash Chart and FusionCharts both require that you install the Flash plugin.
All the examples I have found for Charts have data that is hard-coded into the example. I want to get the data more dynamically, so I decided to start with a database table. This example shows how to set the PHP code up to do this and it could be extended to other uses without too much trouble.
Requirements and installation:
This sample was created with version 6.xx of Drupal. The Charts module is the only module you will need to download. Once it is downloaded from the Drupal website, copy it to the directory “sites/all/modules” in your Drupal installation. If that directory doesn't exist, go ahead and make it first.

Once the module is installed, go to “Administer->Site building->Modules” and activate the following:

Charts

• Charts
• GD Image Chart
• Google

Core

• PHP filter – This allows us to embed PHP code in a node.

Compatibility with other programs

There is a compatibility issue between the Charts module and the Charts and Graphs module, so if you install both of them you will get an error message even though they will run. For more information on this, please go to http://drupal.org/node/889994.

Creating the chart

To take advantage of the preestablished connections that are set up in Drupal, it is important that you locate your table in the database being used for your Drupal installation. If you do not know what that database is, look in the sites/default/settings.php to find the name.

The next step after creating the table is to create a node for the display. My example uses the “Page” content type for this and you can recreate it by doing the following:

• Go to “Create content” and select “Page”.
• Give it a name. I called mine “Test Chart”.
• If you want, you can enter a menu title here to which the page can be linked. Do this by:
  • Enter a name under “Menu Settings->Menu link title:”
  • Select under “Parent item:”. This will make the menu display with your other main menu items.

The chart is created by entering a PHP code snippet into the body of the node. So under the title “Body”, type in the following:

echo "at the top";
$values = array();
$results = db_query("select chartvals from {chart_test}");

$ii = 0;
while ($next_val = db_fetch_object($results)){
  $values[$ii] = $next_val->chartvals;
  $ii++;
}

$chart = array(
  '#plugin'    => 'google', // Google Charts API will be used
  '#type'     => 'line2D', // A simple line chart
  '#height'   => 100, // in pixels
  '#width'    => 200, // in pixels

   $values, // This is the contents of the array build above
);
return charts_chart($chart);

• Under “Input format”, select “PHP code”.
• Make sure that, under “Published”, the box beside “Published” is checked.
• Click “Save” and a small line chart should appear in the body of the node.

When you have finished all these steps, you should see your chart.