phpGrid, phpChart Integration

phpgrid phpchart integration
Launch Demo

 

It’s easy to combine phpGrid and phpChart to create a seamless integrated, interactive, and completely web-based content and report management tool.

Setup

Before you start, you must download phpGrid and phpChart. Both downloads are free.

It’s important to install phpGrid and phpChart in separate folders. The chart below shows the folder structure we used in our example. Both phpGrid and phpChart resides in a separate folder within “phpGrid_phpChart” folder which is found under the web root folder “htdocs”. This is the RECOMMENDED folder hierarchy.

htdocs
+-- phpGrid_phpChart
|   |-- phpGrid
|   |   +-- conf.php
|   |-- phpChart
|   |   +-- conf.php
|   +-- phpgrid_phpchart_integration.php
 

Once the folder structure is ready, define the phpChart SCRIPTPATH value in the file conf.php.

phpChart conf.php

1
define('SCRIPTPATH','/phpGrid_phpChart/phpChart/');

Programming

Now, we can move on to the actual integration. First all, add the instruction to include both the conf.php files for each product at the start of the script.

1
2
require_once("phpGrid/conf.php");
require_once("phpChart/conf.php");

Then, create the chart with phpChart. Here in this example we have two charts. The first chart uses a single series of data. The second one has two series and renders a bar chart instead of the default line chart. All the phpChart methods and properties documents are available on phpChart.com. Pass “null” as the series data if you don’t want to have any initial data in the chart.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// first phpChart with one series
$pc = new C_PhpChartX(array(array(null)), 'Graph');
$pc->set_axes(array(
        'xaxis'=> array('label'=>'Buy Price'),
        'yaxis'=> array('label'=>'$')
    ));
$pc->draw(800,500);

// second phpChart with two series
$pc2 = new C_PhpChartX(array(array(null), array(null)), 'Graph2');
$pc2->set_axes(array(
        'xaxis'=> array(
            'label'=>'Buy Price/MSRP (by Product Code)'),
        'yaxis'=> array('label'=>'$')
    ));
$pc2->set_animate(true);
$pc2->set_legend(array('show'=>true,'location'=>'nw'));
$pc2->draw(800,500);

The final part is to code the grid. In our example, we use the “products” database table.

The most important part of the grid is the custom event handler “jqGridLoadComplete”. It is here that the phpGrid and phpChart integration happens. In our example, the values in both the “buyPrice” and “MSRP” columns are retrieved and stored in the arrays d1 and d2. You can replace these columns with columns with the data stored in any field in your table. GraphData1 and GraphData2 are used to store the series of dataset that later will be used to plot our charts created earlier.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$dg = new C_DataGrid("SELECT * FROM products", "productCode", "products");
$dg->enable_edit('FORM');
$onGridLoadComplete = <<<ONGRIDLOADCOMPLETE
function(status, rowid)
{
    var GraphData1 = [];
    var GraphData2 = [];

    d1 = $('#products').jqGrid('getCol', 'buyPrice', false);
    d2 = $('#products').jqGrid('getCol', 'MSRP', false);
    npoints = d1.length;
    for(var i=0; i < npoints; i++){
        GraphData1[i] = [i+1, parseInt(d1[i])];
    }
    _Graph.series[0].data = GraphData1;
    _Graph.replot({resetAxes:true});

    for(var i=0; i < npoints; i++){
        GraphData1[i] = [i+1, parseInt(d1[i])];
        GraphData2[i] = [i+1, parseInt(d2[i])];
    }
    _Graph2.series[0].data = GraphData1;
    _Graph2.series[1].data = GraphData2;
    _Graph2.replot({resetAxes:true});
}
ONGRIDLOADCOMPLETE
;
$dg->add_event("jqGridLoadComplete", $onGridLoadComplete);
$dg->set_col_property('MSRP', array('classes'=>'msrp_bg'));   // change column background color
$dg->set_col_property('buyPrice', array('classes'=>'buyprice_bg'));  // change column background color
$dg->enable_search(true);
$dg->display();

To easily recognize the data source, we set the CSS class so that the datagrid column background color to matches the series color in phpChart.

1
2
.msrp_bg { background-color: #4BB2C5 !important; }
.buyprice_bg {  background-color: #F8C05B !important; }

The COMPLETE example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
<?php
require_once("phpGrid/conf.php");
require_once("phpChart/conf.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>phpGrid phpChart Integration Example</title>
</head>
<body>
<STYLE>
.msrp_bg {
    background-color: #4BB2C5 !important;
}
.buyprice_bg {
    background-color: #F8C05B !important;
}
</STYLE>

<p>Try do a quick search or do a sort. Observe the graph updates in real-time.</p>

<table><tr><td>
<?php
// first phpChart with one series
$pc = new C_PhpChartX(array(array(null)), 'Graph');
$pc->set_axes(array(
        'xaxis'=> array('label'=>'Buy Price'),
        'yaxis'=> array('label'=>'$')
    ));
$pc->draw(600,300);
?>
</td>
<td>
<?php
// second phpChart with two series
$pc2 = new C_PhpChartX(array(array(null), array(null)), 'Graph2');
$pc2->set_axes(array(
        'xaxis'=> array(
            'label'=>'Buy Price/MSRP (by Product Code)'),
        'yaxis'=> array('label'=>'$')
    ));
$pc2->set_animate(true);
$pc2->set_legend(array('show'=>true,'location'=>'nw'));
$pc2->draw(600,300);
?>
</td></tr></table>

<?php
$dg = new C_DataGrid("SELECT * FROM products", "productCode", "products");
$dg->enable_edit('FORM');

$onGridLoadComplete = <<<ONGRIDLOADCOMPLETE
function(status, rowid)
{
    var GraphData1 = [];
    var GraphData2 = [];

    d1 = $('#products').jqGrid('getCol', 'buyPrice', false);
    d2 = $('#products').jqGrid('getCol', 'MSRP', false);
    npoints = d1.length;
    for(var i=0; i < npoints; i++){
        GraphData1[i] = [i+1, parseInt(d1[i])];
    }
    _Graph.series[0].data = GraphData1;
    _Graph.replot({resetAxes:true});

    for(var i=0; i < npoints; i++){
            GraphData1[i] = [i+1, parseInt(d1[i])];
            GraphData2[i] = [i+1, parseInt(d2[i])];
    }
    _Graph2.series[0].data = GraphData1;
    _Graph2.series[1].data = GraphData2;
    _Graph2.replot({resetAxes:true});
}
ONGRIDLOADCOMPLETE
;

$dg->add_event("jqGridLoadComplete", $onGridLoadComplete);
$dg->set_col_property('MSRP', array('classes'=>'msrp_bg'));
$dg->set_col_property('buyPrice', array('classes'=>'buyprice_bg'));
$dg->enable_search(true);
$dg->display();
?>
</body>
</html>

That’s all there is to it! Try a quick search or sort and observe the graph updating in real-time.

Give it a try! | Download Integration Example File

Technical Notes

  • The Javascript used in the custom event handler in phpGrid is the bread and butter of this integration.
  • Set the array to null for the initial graph.
  • For the creation of the initial plot you can pass series of data in a 1-D array. jqPlot internally converts the data into a 2-D array. However, keep in mind that, this does not happen on .replot().
  • If you should get the following error message
    1
    Notice: Constant DEBUG already defined in /phpGrid/conf.php on line 2

    This happens because both phpChart and phpGrid have a DEBUG constant, you need to comment out of them in conf.php

    1
    // define('DEBUG', true);

Using phpChart Lite

One can also use phpChart free Lite version to integrate with phpGrid. However, since phpChart Lite only supports a single chart, there are some limitations to the integration above. For example, because the graph name is hard coded to ‘__chart1’ in Lite (with two underscore), in the following

1
2
    _Graph.series[0].data = GraphData1;
    _Graph.replot({resetAxes:true});

_Graph must be replaced with ___chart1 (a triple underscore!)

1
2
3
4
     ___chart1.series[0].data = GraphData1;
     ___chart1.replot({
         resetAxes: true
     });