Zeta Components - high quality PHP components

eZ Components - GraphDatabaseTieIn

Table of Contents

Introduction

The GraphDatabaseTieIn allows you to directly display results from a database query in a graph using ezcGraph. All data represented in PDOStatements, for example returned by the database component, can be displayed.

Class overview

This section gives you an overview of the most important classes.

ezcGraphDatabaseDataSet
Extends the ezcGraphDataSet to read data from a PDOStatement and prepare it for the usage as a dataset in the graph component.

Setup

For the examples we expect a simple database setup, so that we are able to test our examples. For this we create a table with some statistical data to be used in the graphs.

  1. <?php
  3. require_once 'tutorial_autoload.php';
  5. $db = ezcDbFactory::create( 'sqlite://:memory:' );
  6. ezcDbInstance::set( $db );
  8. // Create test table
  9. $db->exec( 'CREATE TABLE browser_hits ( id INT, browser VARCHAR(255), hits INT )' );
  11. // Insert some data
  12. $db->exec( "INSERT INTO browser_hits VALUES ( NULL, 'Firefox', 2567 )" );
  13. $db->exec( "INSERT INTO browser_hits VALUES ( NULL, 'Opera', 543 )" );
  14. $db->exec( "INSERT INTO browser_hits VALUES ( NULL, 'Safari', 23 )" );
  15. $db->exec( "INSERT INTO browser_hits VALUES ( NULL, 'Konquror', 812 )" );
  16. $db->exec( "INSERT INTO browser_hits VALUES ( NULL, 'Lynx', 431 )" );
  17. $db->exec( "INSERT INTO browser_hits VALUES ( NULL, 'wget', 912 )" );
  19. ?>

We first include the common autoload file to set up the autoload for eZ Components. Then, in line 5, we connect to a SQLite memory database, we later fill up with some data representing a random browser statistic. The usage idescription of PDO and the database component can be found at the dedicated documentation pages.

Usage

Default behaviour

To create a simple pie chart we just select the data and add a new dataset, created from the resulting statement, to a new chart.

  1. <?php
  3. require_once 'tutorial_insert_data.php';
  5. // Receive data from database
  6. $db = ezcDbInstance::get();
  7. $query = $db->createSelectQuery();
  8. $query
  9.     ->select( 'browser', 'hits' )
  10.     ->from( 'browser_hits' );
  11. $statement = $query->prepare();
  12. $statement->execute();
  14. // Create chart from data
  15. $chart = new ezcGraphPieChart();
  16. $chart->title = 'Browser statistics';
  18. $chart->data['browsers'] = new ezcGraphDatabaseDataSet( $statement );
  20. $chart->render( 400, 200, 'tutorial_simple.svg' );
  22. ?>

The query builder we use to create the select query in line 8 to 10 is described in detail in the database components documentation. In this example all values from the columns hits and browser are selected from the table browser_hits. The result of the query is available in $statement after the query was executed. The $statement object is an instance of the PDOStatement class.

The creation of charts is described in detail in the graph components documentation. In this example we create a simple pie chart, set a title for the chart and add a new dataset. To directly use a PDOStatement as a data source an instance of ezcGraphDatabaseDataSet is created with the $statement as the first parameter. By default the first column is used as index and the second column as values for the indices. The result is the pie chart we expected.

Simple pie chart exampleSimple pie chart example

Single column

  1. <?php
  3. require_once 'tutorial_insert_data.php';
  5. // Receive data from database
  6. $db = ezcDbInstance::get();
  7. $query = $db->createSelectQuery();
  8. $query
  9.     ->select( 'browser', 'hits' )
  10.     ->from( 'browser_hits' );
  11. $statement = $query->prepare();
  12. $statement->execute();
  14. // Create chart from data
  15. $chart = new ezcGraphPieChart();
  16. $chart->title = 'Browser statistics';
  18. $chart->data['browsers'] = new ezcGraphDatabaseDataSet( $statement );
  20. $chart->render( 400, 200, 'tutorial_simple.svg' );
  22. ?>

When only a single column is returned by the select query the values are considered as a zero indexed array. This might be useful to display them in line or bar charts.

The created data set may be used in the same way like all other data sets, which can be seen in line 20 in the example above, where a average polynomial data set is created from the database data set. More documentation on average datasets can be found in the graph tutorial.

Line chart exampleLine chart example

Multiple columns

You also may specify which column should be used as a key and which column should be used as a value in the created dataset. This is particulary useful when dealing with more then two columns.

  1. <?php
  3. require_once 'tutorial_insert_data.php';
  5. // Receive data from database
  6. $db = ezcDbInstance::get();
  7. $query = $db->createSelectQuery();
  8. $query
  9.     ->select( '*' )
  10.     ->from( 'browser_hits' );
  11. $statement = $query->prepare();
  12. $statement->execute();
  14. // Create chart from data
  15. $chart = new ezcGraphPieChart();
  16. $chart->title = 'Browser statistics';
  17. $chart->legend = false;
  19. $chart->data['browsers'] = new ezcGraphDatabaseDataSet( 
  20.     $statement,
  21.     array(
  22.         ezcGraph::KEY   => 'browser',
  23.         ezcGraph::VALUE => 'hits',
  24.     )
  25. );
  27. // Some graph output formatting
  28. $chart->renderer = new ezcGraphRenderer3d();
  30. $chart->renderer->options->pieChartGleam = .3;
  31. $chart->renderer->options->pieChartGleamColor = '#FFFFFF';
  32. $chart->renderer->options->dataBorder = false;
  34. $chart->renderer->options->pieChartShadowSize = 5;
  35. $chart->renderer->options->pieChartShadowColor = '#000000';
  37. $chart->renderer->options->pieChartSymbolColor = '#55575388';
  39. $chart->renderer->options->pieChartHeight = 5;
  40. $chart->renderer->options->pieChartRotation = .8;
  42. // Render
  43. $chart->render( 400, 150, 'tutorial_multiple.svg' );
  45. ?>

In this example all columns from the table are selected using the *, but the array starting in line 21 defines which columns are used for keys and values. There are two array keys, which are constants defined in ezcGraph, referencing the name of the column to use.

PDO by default lowercases all column names, see PDO::setAttribute() for details. If the column names you pass to the dataset definition array are not lowercase, you either need to change the PDO::ATTR_CASE attribute of your PDO connection instance, or lowercase the names passed to the definition array. Otherwise this will throw ezcGraphDatabaseMissingColumnException exceptions.

Starting at line 28 we change the renderer and enhance the output a bit. This is described in more detail in the 3D renderer section in the graph tutorial.

Pie chart from multiple columnsPie chart from multiple columns

More information

For more information, see the GraphDatabaseTieIn API documentation.