Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-17
19
Medium Priority
?
6,487 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

886 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