Solved

How to plot multiple lines on a line graph using Jpgraph in PHP from MYSQL

Posted on 2011-02-17
19
6,139 Views
Last Modified: 2013-12-13
Hello!

I am using PHP Jpgraph to plot a line graph for data read from a MYSQL table.

 I have managed to plot one line from the results, but I want to plot additional lines for each value in one column called "server".

My code is displayed below but here is an example of the table from MYSQL:

time         number          server
11:00          456             s1
12:00          433             s1
11:00          132             s2
12:00           145            s2

The result would then be two lines on the line graph for s1 & s2, showing the corresponding data.

I hope I make sense. My theory is that the answer lies in the foreach / array loops.

Thanks for any help given.
//Build Query
$qt=mysql_query("SELECT * FROM stats_players GROUP BY server");

$servers = array();
while ($row = mysql_fetch_array($qt))
{
	$servers[$row['server']] = array($row['number'], $row['date']);
}

// Setup the graph
$graph = new Graph(500,400);
$graph->SetScale("textlin");

$theme_class=new UniversalTheme;

$graph->SetTheme($theme_class);
$graph->img->SetAntiAliasing(false);
$graph->title->Set($server_d);
$graph->img->SetMargin(70,15,30,60); // Sets the margin.  L, R, T, B
$graph->SetBox(false);

$graph->img->SetAntiAliasing();

$graph->yaxis->HideZeroLabel();
$graph->yaxis->HideLine(false);
$graph->yaxis->HideTicks(false,false);
$graph->yaxis->title->Set('Player Logs');
$graph->yaxis->SetTitleMargin(50);

$graph->xgrid->Show();
$graph->xgrid->SetLineStyle("solid");
$graph->xaxis->SetTickLabels($datay);
$graph->xaxis->SetLabelAngle(60);
$graph->xgrid->SetColor('#E3E3E3');

foreach ($servers as $p1 => $server)
{

// Create lines for each "server" listed in column
$p1 = new LinePlot($server);
$graph->Add($p1);
$p1->mark->SetType(MARK_FILLEDCIRCLE,'',1.0);
$p1->mark->SetColor('#6495ED');
$p1->SetColor("#6495ED");
$p1->SetLegend($p1);

}

$graph->legend->SetFrameWeight(1);

// Output line
$graph->Stroke();

?>

Open in new window

0
Comment
Question by:matt_p002
  • 10
  • 9
19 Comments
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34914860
Can you post some more of the script (the classess you use etc.)
I've downloaded jpgraph but cannot make it work with this part of the code.
0
 

Author Comment

by:matt_p002
ID: 34915203
Hi

Thanks for your reply.

The only code before that quoted is:

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

require_once "includes.php";

require_once ('jpgraph/jpgraph.php');
require_once ('jpgraph/jpgraph_line.php');
?>

With jpgraph installed, it should still give you an error as it doesnt like my code. However it will be a jpgraph error over a  php one.

Regards

Matt
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34915295
The table structure you provided hasn't got $row['date'] field. Can you post the whole table structure with part of the data ?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:matt_p002
ID: 34915362
Sorry thats an error. $row['date'] should be $row['time']

Matt
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34915378
I've got
JpGraph Error: 25070 Either X or Y data arrays contains non-numeric values. Check that the data is really specified as numeric data and not as strings. It is an error to specify data for example as '-2345.2' (using quotes).

This can be related to the db structure I've created. Can you post the table slq creation script ?
0
 

Author Comment

by:matt_p002
ID: 34915403
That wll be because of another error in my posted script.

Replace:

$graph->xaxis->SetTickLabels($datay);

with:

$graph->xaxis->SetTickLabels(array('A','B','C','D'));

Matt
0
 

Author Comment

by:matt_p002
ID: 34915420
The error you should then face (which I also do hence my question) is:

JpGraph Error: 25068 A plot has an illegal scale. This could for example be that you are trying to use text auto scaling to draw a line plot with only one point or that the plot area is too small. It could also be that no input data value is numeric (perhaps only '-' or 'x')

If I remove the foreach loop the graph works with one line.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34915427
No change.
0
 

Author Comment

by:matt_p002
ID: 34915554
Ok try this code which works for displaying one line on the graph.


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

require_once "includes.php";

require_once ('jpgraph/jpgraph.php');
require_once ('jpgraph/jpgraph_line.php');

$server_d = 's1';

//Build Query
$qt=mysql_query("SELECT * FROM stats_players WHERE server='$server_d'");

// Create Data Array
$datax = array();
$datay = array();

while ($row = mysql_fetch_array($qt))
{

	$datax[] = $row['number'];
	//Prepare Time display
	$a = explode(':', $row['time']);
	$datay[] = "{$row['day']} {$a[0]}:{$a[1]}";
	
}


// Setup the graph
$graph = new Graph(500,400);
$graph->SetScale("textlin");

$theme_class=new UniversalTheme;

$graph->SetTheme($theme_class);
$graph->img->SetAntiAliasing(false);
$graph->title->Set($server_d);
$graph->img->SetMargin(70,15,30,60); // Sets the margin.  L, R, T, B
$graph->SetBox(false);

$graph->img->SetAntiAliasing();

$graph->yaxis->HideZeroLabel();
$graph->yaxis->HideLine(false);
$graph->yaxis->HideTicks(false,false);
$graph->yaxis->title->Set('Player Logs');
$graph->yaxis->SetTitleMargin(50);

$graph->xgrid->Show();
$graph->xgrid->SetLineStyle("solid");
$graph->xaxis->SetTickLabels($datay);
$graph->xaxis->SetLabelAngle(60);
$graph->xgrid->SetColor('#E3E3E3');

// Create the first line
$p1 = new LinePlot($datax);
$graph->Add($p1);
$p1->mark->SetType(MARK_FILLEDCIRCLE,'',1.0);
$p1->mark->SetColor('#6495ED');
$p1->SetColor("#6495ED");
$p1->SetLegend('legend');

/* Create the second line
$p2 = new LinePlot($datay2);
$graph->Add($p2);
$p2->SetColor("#B22222");
$p2->SetLegend('Line 2');

// Create the third line
$p3 = new LinePlot($datay3);
$graph->Add($p3);
$p3->SetColor("#FF1493");
$p3->SetLegend('Line 3');
*/

$graph->legend->SetFrameWeight(1);

// Output line
$graph->Stroke();

Open in new window

0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34915699
Ok got it. Give me a sec.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34915897
I've managed to print bothe of the lines, tell me if this is what you need, then we can go further (this works only for s1 and s2

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

require_once ('../jpgraph.php');
require_once ('../jpgraph_line.php');

// FOR DB CONNECTION
mysql_connect("localhost","root","root");
mysql_select_db("test");

$server_d = 's1';

//Build Query
$qt=mysql_query("SELECT * FROM stats_players WHERE server='$server_d'");

// Create Data Array
$datax = array();
$datay = array();

while ($row = mysql_fetch_array($qt))
{

	$datax[] = $row['number'];
	//Prepare Time display
	$a = explode(':', $row['time']);
	$datay[] = "{$row['day']} {$a[0]}:{$a[1]}";
	
}


// Setup the graph
$graph = new Graph(500,400);
$graph->SetScale("textlin");

$theme_class=new UniversalTheme;

$graph->SetTheme($theme_class);
$graph->img->SetAntiAliasing(false);
$graph->title->Set($server_d);
$graph->img->SetMargin(70,15,30,60); // Sets the margin.  L, R, T, B
$graph->SetBox(false);

$graph->img->SetAntiAliasing();

$graph->yaxis->HideZeroLabel();
$graph->yaxis->HideLine(false);
$graph->yaxis->HideTicks(false,false);
$graph->yaxis->title->Set('Player Logs');
$graph->yaxis->SetTitleMargin(50);

$graph->xgrid->Show();
$graph->xgrid->SetLineStyle("solid");
$graph->xaxis->SetTickLabels($datay);
$graph->xaxis->SetLabelAngle(60);
$graph->xgrid->SetColor('#E3E3E3');

print_r($datax);

// Create the first line
$p1 = new LinePlot($datax);
$graph->Add($p1);
$p1->mark->SetType(MARK_FILLEDCIRCLE,'',1.0);
$p1->mark->SetColor('#6495ED');
$p1->SetColor("#6495ED");
$p1->SetLegend('legend');

/* Create the second line
$p2 = new LinePlot($datay2);
$graph->Add($p2);
$p2->SetColor("#B22222");
$p2->SetLegend('Line 2');

// Create the third line
$p3 = new LinePlot($datay3);
$graph->Add($p3);
$p3->SetColor("#FF1493");
$p3->SetLegend('Line 3');
*/

$graph->legend->SetFrameWeight(1);

// Output line
$graph->Stroke();
?>

Open in new window

0
 

Author Comment

by:matt_p002
ID: 34916005
This only plots one line on the graph for s1.

Am I missing something?
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34916033
Try with this - ther is foreach loop. I think you got the wrong query

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


require_once ('../jpgraph.php');
require_once ('../jpgraph_line.php');


mysql_connect("localhost","root","root");
mysql_select_db("test");

$server_d = 's1';

//Build Query
$qt=mysql_query("SELECT * FROM stats_players order by server");

// Create Data Array
$datax['s1'] = array();
//$datay = array();

$i = 0; $j = 0;

while ($row = mysql_fetch_array($qt))
{
    if(is_array($datax[$row['server']]));
    else{$datax[$row['server']] = array();}

    //$servers[$row['server']] = array($row['number'], $row['date']);

    array_push($datax[$row['server']] ,$row['number']);
    //if($row['server'] == 's1'){
    //    $datax[$row['server']][$i] = $row['number']; $i++;
	//}
	//else{
    //    $datax[$row['server']][$j] = $row['number']; $j++;	
	//}
	//$datax[$i][1] = $row['time'];
	//$datax[$i][1] = $row['time'];
	//Prepare Time display
	$a = explode(':', $row['time']);
	$datay[] = "{$row['day']} {$a[0]}:{$a[1]}";
	
	//$i++;
}


// Setup the graph
$graph = new Graph(500,400);
$graph->SetScale("textlin");

$theme_class=new UniversalTheme;

$graph->SetTheme($theme_class);
$graph->img->SetAntiAliasing(false);
$graph->title->Set($server_d);
$graph->img->SetMargin(70,15,30,60); // Sets the margin.  L, R, T, B
$graph->SetBox(false);

$graph->img->SetAntiAliasing();

$graph->yaxis->HideZeroLabel();
$graph->yaxis->HideLine(false);
$graph->yaxis->HideTicks(false,false);
$graph->yaxis->title->Set('Player Logs');
$graph->yaxis->SetTitleMargin(50);

$graph->xgrid->Show();
$graph->xgrid->SetLineStyle("solid");
$graph->xaxis->SetTickLabels($datay);
$graph->xaxis->SetLabelAngle(60);
$graph->xgrid->SetColor('#E3E3E3');

/*
foreach ($servers as $p1 => $server)
{

// Create lines for each "server" listed in column
$p1 = new LinePlot($server);
$graph->Add($p1);
$p1->mark->SetType(MARK_FILLEDCIRCLE,'',1.0);
$p1->mark->SetColor('#6495ED');
$p1->SetColor("#6495ED");
$p1->SetLegend($p1);

}
*/

//print_r($datax);

foreach($datax as $server => $value){
    $p1 = new LinePlot($datax[$server]);
    $graph->Add($p1);
    $p1->mark->SetType(MARK_FILLEDCIRCLE,'',1.0);
    $p1->mark->SetColor('#6495ED');
    $p1->SetColor("#6495ED");
    $p1->SetLegend('legend');
}

/*
// Create the first line
$p1 = new LinePlot($datax['s1']);
$graph->Add($p1);
$p1->mark->SetType(MARK_FILLEDCIRCLE,'',1.0);
$p1->mark->SetColor('#6495ED');
$p1->SetColor("#6495ED");
$p1->SetLegend('legend');

$p1 = new LinePlot($datax['s2']);
$graph->Add($p1);
$p1->mark->SetType(MARK_FILLEDCIRCLE,'',1.0);
$p1->mark->SetColor('#6495ED');
$p1->SetColor("#6495ED");
$p1->SetLegend('legend');
/*
// Create the second line
$p2 = new LinePlot($datay2);
$graph->Add($p2);
$p2->SetColor("#B22222");
$p2->SetLegend('Line 2');

// Create the third line
$p3 = new LinePlot($datay3);
$graph->Add($p3);
$p3->SetColor("#FF1493");
$p3->SetLegend('Line 3');
*/

$graph->legend->SetFrameWeight(1);

// Output line
$graph->Stroke();
?>

Open in new window

0
 

Author Comment

by:matt_p002
ID: 34917126
Hi

Thanks for the code. Sadly I have a Jpgraph error:

JpGraph Error: 25121 Empty input data array specified for plot. Must have at least one data point.

Any ideas?
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34917945
It might be related to data, I've added more to see if this works. How much rows do you have in table ? What do they have inside ?
0
 

Author Comment

by:matt_p002
ID: 34917971
I have at least 6 for each server. In the table I have over 70 entries as every 24 hours another 4 are added for each server.
0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 500 total points
ID: 34918236
This data

11:00	456	s1	2011-02-17
12:00	433	s1	2011-02-17
11:00	132	s2	2011-02-17
12:00	145	s2	2011-02-17
11:00	157	s3	2011-02-17
11:30	512	s3	2011-02-17
12:00	277	s3	2011-02-17

Open in new window


produces this
 image
0
 

Author Comment

by:matt_p002
ID: 34919907
Got it to work thanks. One problem I have noticed which I did not anticipate though is the fact 4 readings are taken each day for the relevant server.

Therfore my table now looks like this:

time         number          server             day
11:00          456             s1                   15th
12:00          433             s1                   15th
11:00          132             s2                   16th
12:00           145            s2                   16th
18:00           147            s2                   16th

How would I change the query to show the total number for each day for each server on the graph opposed to every reading for every day?

I tried this to no avail; I get a PHP error which I assume is because of group by and order by. With just the group by and no order by, I have a Jpgraph error.

//Build Query
$qt=mysql_query("SELECT SUM(number) as total, time, day, server FROM stats_players GROUP BY day ORDER BY server,number,time ASC");

then:

array_push($datax[$row['server']] ,$row['total']);

Any ideas?

Thanks

Matt
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34920134
Grouping the query produces only one row per server value which is the y-axis. Only one value cannot produce a "single value" graph. You would have to delete grouping from the query and divide the graphs into days, example (...)  where day = '15th'.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question