Solved

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

Posted on 2011-02-17
19
5,952 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
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 …

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now