Jpgraph using data from mySQL

PHP
1

Hey guys,

I’m trying to create a graph from some data stored in a mySQL DB.

My table contains data from a wireless drive but I’m only concerned with the Encryption field, which has WEP, WPA or None as text.

My problem is I’m trying to create a pie chart showing the percentage of each type of encryption, however this data isn’t numeric its throwing an error, and I dont know how to go about getting it to numeric data.

I was thinking of using COUNT but I think the code will start to get bloated.

I get the error ‘Illegal Pie Plot, Sum of all data is zero for pie plot’.

Any help appreciated.

Ronan

2

Can you show us a couple of rows of typical data from the database and what you are hoping to plot.

Mike

3

Will do mike …

Heres my table …


CREATE TABLE wireless (
    id int(10) unsigned NOT NULL auto_increment,
    NetType text default NULL,
    ESSID text default NULL,
    BSSID text default NULL,
    Info text default NULL,
    Channel int(2) unsigned NULL,
    Cloaked text default NULL,
    Encryption text default NULL,
    MaxRate int(5) unsigned NULL,
    Carrier text default NULL,
    BestSignal int(5) unsigned NULL,
    BestQuality int(5) unsigned NULL,
    GPSMinLat float NULL,
    GPSMaxLat float NULL,
    GPSMinLon float NULL,
    GPSMaxLon float NULL,
    GPSBestLat float NULL,
    GPSBestLon float NULL,
    PRIMARY KEY (id)
);

Heres some sample data]


23, '', '3Com', '00:0F:CB:9F:DB:96', '', 11, 'No', 'WEP,TKIP,WPA', 11, '', 0, 0, 53.3763, 53.3776, -6.22557, -6.22485, 0, 0

24, '', 'Home', '00:13:49:A8:36:13', '', 11, 'No', 'None', 22, '', 0, 0, 53.3761, 53.3777, -6.22559, -6.22491, 0, 0

25, '', 'linksys', '00:0F:CC:25:BE:80', '', 7, 'No', 'WEP', 22, '', 0, 0, 53.3775, 53.3775, -6.22491, -6.22485, 0, 0

I have Encryption types of WEP, None and ‘WEP,TKIP,WPA’.

I want to create a pie chart that will show what percentage of the results have WEP, None and ‘WEP,TKIP,WPA’.

When I run the manual sql query for each encryption type I get the following values:


SELECT count(*) FROM wireless w where Encryption = "WEP";

Value: 1900


SELECT count(*) FROM wireless w where Encryption = "None";

Value: 795


SELECT count(*) FROM wireless w where Encryption like "%WPA%";

Value: 448

My pie chart should have 3 segments, one to represent each of the values above and if possible to have a legend that shows which each segment represents.

Regards,

Ronan

4

Ah, I got it now.

So you can get the data out of the database ok, but just can’t draw the graph. I can’t help you with the graph as I don’t know the jpgraph library.

I would probably join those queries with a “union”. It will return one column with a row for each count.


SELECT count(*) as counts FROM wireless where Encryption = "WEP"
union
SELECT count(*) FROM wireless where Encryption = "None"
union
SELECT count(*) FROM wireless where Encryption like "%WPA%"

Info: http://dev.mysql.com/doc/refman/5.0/en/union.html

Good luck with the pie chart.

Mike

5

Cheers mike,

got it sorted late last nite - heres my full code …


<?php
include_once ("db.inc");
include_once ("src/jpgraph.php");
include_once ("src/jpgraph_pie.php");
include_once ("src/jpgraph_pie3d.php");

$connection=mysql_connect ($host, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// Set the active mySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\\'t use db : ' . mysql_error());
}

// Select all the rows in the markers table
$sqlTotal = "SELECT count(*) AS Number FROM wireless";
$sqlWPA = "SELECT count(*) AS Number FROM wireless w where Encryption LIKE '%WPA%'";
$sqlNone = "SELECT count(*) AS Number FROM wireless w where Encryption = 'None'";
$sqlWEP = "SELECT count(*) AS Number FROM wireless w where Encryption = 'WEP'";

$total = mysql_query($sqlTotal);
$wpa = mysql_query($sqlWPA);
$none = mysql_query($sqlNone);
$wep = mysql_query($sqlWEP);

while($resultTotal = mysql_fetch_array($total))
{
	$totalValue = $resultTotal['Number'];
}

while($resultWPA = mysql_fetch_array($wpa))
{
	$wpaValue = $resultWPA['Number'];
}

while($resultNone = mysql_fetch_array($none))
{
	$noneValue = $resultNone['Number'];
}

while($resultWEP = mysql_fetch_array($wep))
{
	$wepValue = $resultWEP['Number'];
}


$data = array($noneValue, $wepValue, $wpaValue);

$graph = new PieGraph(410, 310,"auto");
$graph->SetShadow();
$graph->title->Set("Wireless Encryption Schemes in Dublin, Ireland");
$graph->title->SetFont(FF_FONT1,FS_BOLD);

$p1 = new PiePlot3D($data);
$p1->SetSize(.3);
$p1->SetCenter(0.45);
$p1->SetStartAngle(300);
$p1->SetAngle(45);
$p1->SetTheme('earth');

$p1->value->SetFont(FF_FONT1,FS_BOLD);
$p1->SetLabelType(PIE_VALUE_PER);

$legends = array('None','WEP','WPA');

$p1->SetLegends($legends);



$a = array_search(max($data),$data); //Find the position of  maixum value.
$p1->ExplodeSlice($a);

$graph->Add($p1);
$graph->Stroke();
?>

Pretty much the same way you were on about it.

Regards,

Ronan

6

Ah - I’m pleased you got it sorted … jpgraph looks easy to use, I’ll have to check it out.

I’d still go with the union … you will only send 1 query to the database, not 4 :slight_smile:

Regards,

Mike