Plotting data from a MySQL database using jpgraph

First Step: Putting the jpgraph library on the server

The jpgraph library can be downloaded from here. After it is downloaded put it somewhere on the server or local computer you are using it on. It doesn’t matter where you put it exactly. I put it in public_html/jpgraph on my server. There are loads of examples provided with the library. So you can check if everything is working by pointing your browser to one of the example files. You can check out one of the examples by going to this link : http://www.wattnotions.com/jpgraph/src/Examples/centeredlineex01.php.

Grab data from a MySQL DB and plot it.

Using the example graph linked above it was very simple to add in some code to connect to a mysql database and store some numbers in an array. This array is then passed to jpgraph and it plots it. If only one set of numbers (in this case the y-axis) is passed, then jpgraph will automatically make the x-axis starting from 0 and incrementing by 1 for each y point.

The numbers in the MySQL database were randomly generated by a python script. The PhP script to retrieve these numbers and plot them is shown below:

<?php // content="text/plain; charset=utf-8"

define('__ROOT__', dirname(dirname(__FILE__))); 
require_once ('../jpgraph.php');
require_once ('../jpgraph_line.php');
require_once ('../jpgraph_error.php');


$x_axis = array();
$y_axis = array();
$i = 0;

$con=mysqli_connect("localhost","username","password","testdb");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM rand_data");


while($row = mysqli_fetch_array($result)) {
$x_axis[$i] =  $row["data_id"];
$y_axis[$i] = $row["data_val"];
	$i++;

}
	
	

	
	mysqli_close($con);



$graph = new Graph(800,500);
$graph->img->SetMargin(40,40,40,40);	
$graph->img->SetAntiAliasing();
$graph->SetScale("textlin");
$graph->SetShadow();
$graph->title->Set("Example of line centered plot");
$graph->title->SetFont(FF_FONT1,FS_BOLD);


// Use 20% "grace" to get slightly larger scale then min/max of
// data
$graph->yscale->SetGrace(0);


$p1 = new LinePlot($y_axis);
$p1->mark->SetType(MARK_FILLEDCIRCLE);
$p1->mark->SetFillColor("red");
$p1->mark->SetWidth(4);
$p1->SetColor("blue");
$p1->SetCenter();
$graph->Add($p1);

$graph->Stroke();

?>


This is the graph produced:

To embed the generated image in this wordpressed post I used the html img tag with the source pointing to the PHP script:

<img src="http://www.wattnotions.com/jpgraph/src/mygraphs/centeredlineex01.php" />
This entry was posted in MySQL, PHP and tagged , , , . Bookmark the permalink.

4 Responses to Plotting data from a MySQL database using jpgraph

  1. batchloaf says:

    That’s incredibly useful!

    • wattnotions says:

      I like the way its so simple and it just works. The graphs it makes actually look nice which is a big plus also

  2. Pingback: Making an updating graph using javascript and jpgraph |

  3. why the image is like that ? i cant see the graph…

Leave a Reply