Charts

From PmaWiki
Jump to: navigation, search

Contents

[edit] Chart display

Since phpMyAdmin version 3.4.0, you can easily generate charts from a SQL query by clicking the "Display chart" link in the "Query results operations" area:

query result operations.png

A window layer "Display chart" is shown in which you can select the chart type and configure the titles of the chart and axises.

display chart.png


[edit] Chart implementation

  • Image pChart
  • JS is added to make image charts more interactive. (Tooltips)

[edit] Chart generation functions

[edit] PMA_chart_status($data)

This function generates a Pie chart for statistics of query types executed. This chart can be seen in the status page. This function is called from the server_status.php file. First and only parameter is an array which has the following structure: <source lang="php"> array

 'key' => string 'value'
 ...

</source>

[edit] PMA_chart_profiling($data)

This function generates a chart when the profiling of the query is activated. This function is called from the libraries/common.lib.php file. The parameter has the same structure as for the PMA_chart_status($data) function.

[edit] PMA_chart_results($data, &$chartSettings)

This function generates a chart from the query results. This function is called from the tbl_chart.php file. Parameters are:

  • $data is an array representing query results. It has the following structure:

<source lang="php"> array

 0 => 
   array
     'column 0 name' => string 'value at row 0, column 0'
     'column 1 name' => string 'value at row 0, column 1'
     'column ... name' => string 'value at row 0, column ...'
 1 => 
   array
     'column 0 name' => string 'value at row 1, column 0'
     'column 1 name' => string 'value at row 1, column 1'
     'column ... name' => string 'value at row 1, column ...'
 ... =>

</source>

  • $chartSettings is an associative array where each key and value pair sets a different setting for the chart. During the execution of the function this array is changed to include all the settings that were used to generate current chart.

These functions will return HTML and JS code which when rendered will show the chart. All of these functions are situated here: /libraries/chart.lib.php. This file will be just the wrapper around the chart implementation. These functions are mainly used to restructure the input data so that chart generators can understand it.

If you want to support more formats for query results chart you must change this function.

[edit] Data formats for query results chart

Query result must be in a specific format if a chart will be generated from it. For now three formats are recognized.

[edit] One column

First of all a result table of one column and many rows will be drawn as simple bar or line chart where values are drawn on the Y axis and for every Y value X coordinate is incremented by one. Such result table can be generated with the following SQL query:

SELECT  225 AS 'Amount' UNION
SELECT 1157 UNION
SELECT  569 UNION
SELECT  282
one column.png

And the result of this query and the chart is:

Amount
225
1157
569
282

[edit] Two columns

Two column query result table will also generate the same chart as above, but now values for the X axis will also be taken from the table. Every row now corresponds to one point in the chart. Value from the first column is Y value and value from the second column is a value for X axis. Such result table can be generated with such SQL query:

SELECT  225 AS 'Amount', '2009-11' AS 'Month' UNION
SELECT 1157, '2009-12' UNION
SELECT  569, '2010-01' UNION
SELECT  282, '2010-02' UNION
SELECT 2960, '2010-03'
two columns.png

The result of this query and the chart will look like this:

Amount Month
225 2009-12
1157 2010-01
569 2010-02
282 2010-23

[edit] Three columns

Additional, third, column can be used to specify different series that data belongs to. Doing so a chart will be generated which will have multiple points on the same X axis value.

SELECT
  225       AS 'Amount',
  '2009-11' AS 'Month',
  'Tele2'   AS 'Operator' UNION
SELECT  653, '2009-11', 'Omnitel' UNION
SELECT 1157, '2009-12',   'Tele2' UNION
SELECT  855, '2009-12', 'Omnitel' UNION
SELECT  569, '2010-01',   'Tele2' UNION
SELECT  253, '2010-01', 'Omnitel' UNION
SELECT  282, '2010-02',   'Tele2' UNION
SELECT  455, '2010-02', 'Omnitel' UNION
SELECT 2960, '2010-03',   'Tele2' UNION
SELECT 3552, '2010-03', 'Omnitel'

The result table and the chart for this query will be something in the lines of this:

three columns.png
Amount Month Operator
225 2009-11 Tele2
653 2009-11 Omnitel
1157 2009-12 Tele2
855 2009-12 Omnitel
569 2010-01 Tele2
253 2010-01 Omnitel
282 2010-02 Tele2
455 2010-02 Omnitel
2960 2010-03 Tele2
3552 2010-03 Omnitel

When having three rows one can just swap some columns and get a different grouping of values. If the second and third columns are swapped, series are grouped by the date and values for X axis are operator names.

SELECT
  225       AS 'Amount',
  'Tele2'   AS 'Operator',
  '2009-11' AS 'Month' UNION
SELECT  653, 'Omnitel', '2009-11' UNION
SELECT 1157,   'Tele2', '2009-12' UNION
SELECT  855, 'Omnitel', '2009-12' UNION
SELECT  569,   'Tele2', '2010-01' UNION
SELECT  253, 'Omnitel', '2010-01' UNION
SELECT  282,   'Tele2', '2010-02' UNION
SELECT  455, 'Omnitel', '2010-02' UNION
SELECT 2960,   'Tele2', '2010-03' UNION
SELECT 3552, 'Omnitel', '2010-03'

And the result table and chart will look like:

three columns swap.png
Amount Operator Month
225 Tele2 2009-11
653 Omnitel 2009-11
1157 Tele2 2009-12
855 Omnitel 2009-12
569 Tele2 2010-01
253 Omnitel 2010-01
282 Tele2 2010-02
455 Omnitel 2010-02
2960 Tele2 2010-03
3552 Omnitel 2010-03

[edit] History

The charts implementation was started by Martynas Mickevičius during the Google Summer of Code 2010. Some of these formats were discussed in his blog post. That blog post is no longer accurate and is superseded by this wiki article.

Personal tools