Creating graph with php and mysql

[mbarandao]

Good day all:

I'm trying to create a graph using data from mysql on the jpgraph platform. I've been experiencing difficulties with an error 15009 and cannot seem to address the error --lots of reading on the net concerning this error has yield nothing. I'm hoping that someone here is familar with this issue. Here is my code:

<?php
include ("jpgraph/jpgraph.php");
include ("jpgraph/jpgraph_pie.php");
include_once ("jpgraph/jpgraph_pie3d.php");  

$connect = mysql_connect("localhost","jjdjjd",",,,,,");

$sql = "SELECT DISTINCT servicearea FROM additional_cars";
   $RESULT = mysql_db_query("shop","$sql",$connect);
       $records = mysql_num_rows( $RESULT);     // number of unique services
 for($i=0;$i<$records;$i++)                 // for every unique services read the next lines
{
    $row  = mysql_fetch_row($RESULT); 
    $servicearea = $row[0];
 $sql_2     = "SELECT servicearea FROM additional_cars WHERE servicearea = '$servicearea'";
   $RESULT_2 = mysql_db_query("shop","$sql_2",$connect);
   $records_2 = mysql_num_rows( $RESULT_2);
   //echo $records_2;                        // number of unique services
   $row_2 = mysql_fetch_row($RESULT_2);
   $service_used[] = $row_2[0];
}
  $datavalue   = array_merge($service_used);
      
     
 $datax = $datavalue;
 $datay = $datavalue;

$graph = new PieGraph(410, 310,"auto"); 
$graph->SetShadow(); 
$graph->title->Set("Top three services rendered this week"); 
$graph->title->SetFont(FF_FONT1,FS_BOLD); 

$p1 = new PiePlot3D($datax); 
$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($datay);

$p1->SetLegends($legends); 

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

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

The intent of the above code is to pull data from a table column that has different values --specifically, different services being rendered at a shop. The first sql statement queries for all services from the column and then uses the variable $servicearea to query in the second sql statement which determines how many time each unique service is being performed.

I want to graph this in a pie chart, to thus know which of all the services area rendered the most --in percentage.

I trust that someone here is familiar with this and can provide some insight!

Thanks,
Mossa

[nonshatter]

Hi there, I have no idea what that error code is referring to. But I'd recommend checking the values within the arrays you're trying to plot from.
I'd use FusionCharts with PHP for really good charts and documentation.

[mbarandao]

Hi there, I have no idea what that error code is referring to. But I'd recommend checking the values within the arrays you're trying to plot from.
I'd use FusionCharts with PHP for really good charts and documentation.

Thanks for the reply; the error is referring to:

Illegal pie plot. Sum of all data is zero for Pie Plot

I think I'm a bit at lost at this section of the code

$row_2 = mysql_fetch_row($RESULT_2);
$service_used[] = $row_2[0];

whereas $service_used is not carrying any value. I have tried in the following way --still no luck. I tried:

$row_2 = mysql_fetch_row($RESULT_2);
   $servicearea_used[] = $row_2[$servicearea];
   $serviceused_count[] = $row_2[$records_2];

and is met with "Notice: Undefined index" specifically the following:

Notice: Undefined index: Other in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 21

Notice: Undefined offset: 3 in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 22

Notice: Undefined index: Oil Change in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 21

Notice: Undefined offset: 6 in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 22

Notice: Undefined index: Driveshaft & Axle in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 21

Notice: Undefined offset: 1 in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 22

Notice: Undefined index: Steering in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 21

Notice: Undefined offset: 1 in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 22

Notice: Undefined index: Transmission in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 21

Notice: Undefined offset: 1 in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 22

Notice: Undefined index: Air Intake in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 21

Notice: Undefined offset: 1 in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 22

Notice: Undefined index: Engine Mechanical in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 21

Notice: Undefined offset: 1 in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 22

Notice: Undefined index: Brake in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 21

Notice: Undefined offset: 2 in C:\Apache\htdocs\i-cmsys\graph\graphtest5.php on line 22
JpGraph Error: 15009 Illegal pie plot. Sum of all data is zero for Pie Plot

any thoughts!
Mossa

[nonshatter]

Okay, undefined index means you're referring to an array value that doesn't exist. Try putting the data into your $service_area[] array ike this:

$sql_2 = mysql_query("SELECT servicearea FROM additional_cars WHERE servicearea = '$servicearea'");
while($dataplot = mysql_fetch_assoc($sql_2)) //while there are results from your query, put them into the service_used array
{
    $service_used[] =  $dataplot['servicearea'];
}

[mbarandao]

Okay, undefined index means you're referring to an array value that doesn't exist. Try putting the data into your $service_area[] array ike this:

$sql_2 = mysql_query("SELECT servicearea FROM additional_cars WHERE servicearea = '$servicearea'");
while($dataplot = mysql_fetch_assoc($sql_2)) //while there are results from your query, put them into the service_used array
{
    $service_used[] =  $dataplot['servicearea'];
}

Sorry, still getting the same 15009 error!

[nonshatter]

Until you get the values into your array then of course you will still get that error code!

I expect that JPGraph expects the input to be something like:

$data = array(40,21,17,14,23);

At the moment, you're trying to put a whole row into an array, rather than the integers that are needed for the graph.
What exactly are the values you are trying to plot? Try hard coding them into your array first to see if jpgraph actually likes/plots them. Then concentrate on getting the values from database->array->jpgraph

[mbarandao]

Until you get the values into your array then of course you will still get that error code!

I expect that JPGraph expects the input to be something like:

$data = array(40,21,17,14,23);

At the moment, you're trying to put a whole row into an array, rather than the integers that are needed for the graph.
What exactly are the values you are trying to plot? Try hard coding them into your array first to see if jpgraph actually likes/plots them. Then concentrate on getting the values from database->array->jpgraph

A quick note of what I'm trying to achieve:

My mysql databse contains a table which has a column called "servicearea". It is this column which i am trying to create a graph of. The column contains services (ie: oil change, brake service, transmission ect..) rendered on a daily basis by a repair shop. Obviously these services vary from day to day, I want to be able categories these services in accordance of the most rendered and plot this column in the graph.
the values would be something like this:

keys ||| values

Oil change 9
brake services 20
transmission 30

essentially, I would like graph to group together the same services and display in a barchart the total number of entires for each service.

Hardcoding the key and value like this

$datax = array(40,21,17,14,23);
 $datay = array('mossa1','sam2','david','mark','mark2');

returns a blank screen with the missing image icon..

[nonshatter]

Can you get any JPgraph example charts to render? Can you verify that the GD library is installed with your PHP?
This tutorial looks bang on to what you're trying achieve. Have a ganders. Good luck!

[mbarandao]

Can you get any JPgraph example charts to render? Can you verify that the GD library is installed with your PHP?
This tutorial looks bang on to what you're trying achieve. Have a ganders. Good luck!

Thanks for the continued replies. I recoded differently and I'm successful in pulling the data from db and plotting the graph. The complete code is below. If I may ask, however, based on the code below, If want to show on the graph only the arrays where the values are greater than 0, how would such be added to the code?

Here is the code:

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

 $connect = mysql_connect("localhost","0sddd","");
// Set the active mySQL database 
$db_selected = mysql_select_db("shop", $connect); 
if (!$db_selected) { 
  die ('Can\'t use db : ' . mysql_error()); 
} 

$sqlTotal = "SELECT count(*) AS Number FROM servicesrendered"; 
$sqloil = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%oil%'";  
$sqltransmission = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%transmission%'";   
$sqlother = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%other%'";   
$sqlsteering = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%steering%'";   
$sqlbrake = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%brake%'";   
$sqlengine = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%engine%'";   
$sqlai = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%air%'";   //air intake
$sqlclutch = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%clutch%'";   
$sqlcs = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%cooling%'";   //cooling system
$sqldsa = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%driveshaft%'";   //driveshaft&Axle
$sqlexhaust = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%exhaust%'";   
$sqlsuspension = "SELECT count(*) AS Number FROM servicesrendered a where servicearea LIKE '%suspension%'";   

$total = mysql_query($sqlTotal); 
$oil = mysql_query($sqloil); 
$transmission = mysql_query($sqltransmission); 
$other = mysql_query($sqlother); 
$steering = mysql_query($sqlsteering); 
$brake = mysql_query($sqlbrake); 
$engine = mysql_query($sqlengine); 
$air = mysql_query($sqlai); 
$clutch = mysql_query($sqlclutch); 
$cooling = mysql_query($sqlcs); 
$driveshaft = mysql_query($sqldsa); 
$exhaust = mysql_query($sqlexhaust); 
$suspension = mysql_query($sqlsuspension); 


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

while($resultOil = mysql_fetch_array($oil)) 
{ 
    $oilValue = $resultOil['Number']; 
} 

while($resultTransmission = mysql_fetch_array($transmission)) 
{ 
    $transmissionValue = $resultTransmission['Number']; 
} 

while($resultSteering = mysql_fetch_array($steering)) 
{ 
    $steeringValue = $resultSteering['Number']; 
} 
while($resultbrake = mysql_fetch_array($brake)) 
{ 
    $brakeValue = $resultbrake['Number']; 
} 
while($resultengine = mysql_fetch_array($engine)) 
{ 
    $engineValue = $resultengine['Number']; 
} 

while($resultair = mysql_fetch_array($air)) 
{ 
    $airValue = $resultair['Number']; 
} 
while($resultclutch = mysql_fetch_array($clutch)) 
{ 
    $clutchValue = $resultclutch['Number']; 
} 
while($resultcooling = mysql_fetch_array($cooling)) 
{ 
    $coolingValue = $resultcooling['Number']; 
} 
while($resultdriveshaft = mysql_fetch_array($driveshaft)) 
{ 
    $driveshaftValue = $resultdriveshaft['Number']; 
} 
while($resultexhaust = mysql_fetch_array($exhaust)) 
{ 
    $exhaustValue = $resultexhaust['Number']; 
}
while($resultsuspension = mysql_fetch_array($suspension)) 
{ 
    $suspensionValue = $resultsuspension['Number']; 
}
while($resultOther = mysql_fetch_array($other)) 
{ 
    $otherValue = $resultOther['Number']; 
} 
$time= date('m/d/y'.' @ '.'g:i a');


$data = array($transmissionValue, $oilValue, $steeringValue, $brakeValue, $engineValue, $airValue, $clutchValue, $coolingValue, $driveshaftValue, $exhaustValue,$suspensionValue,$otherValue); 
//print_r($data); 

$graph = new PieGraph(510, 350,"auto"); 
$graph->SetShadow(); 
$graph->title->Set("Matrix of Services Rendered--Last Updated $time"); 
$graph->title->SetFont(FF_FONT1,FS_BOLD); 
$graph->SetColor('silver@0.1' ); 

$p1 = new PiePlot3D($data); 
$p1->SetSize(.3); 
$p1->SetCenter(0.50); 
$p1->SetStartAngle(90);  
$p1->SetAngle(60); 
$p1->SetTheme('earth');

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

$legends = array('Transmission','Oil Changes','Steering','Brakes Syst','Engine','Air Intake','Clutch','Cooling System','Driveshaft & Axle','Exhaust','Other'); 
//print_r($legends); 

$p1->SetLegends($legends); 



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

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

[nonshatter]

Great, glad you got it working. If you wanted to omit the null values from the chart, simply make a check using an if statement, something like:

if($transmissionValue != 0)
{
    $data = array($transmissionValue);
}

[mbarandao]

Great, glad you got it working. If you wanted to omit the null values from the chart, simply make a check using an if statement, something like:

if($transmissionValue != 0)
{
    $data = array($transmissionValue);
}

Thanks for the reply; given the extensive code above, where within that code should the if statement be placed? My apologizes, I'm learning much of this on the fly...

Mossa

[120406dev]

i want to create bar chart using google API in php mysql...but how to implement the bar graph??

[pritaeas]

Google has excellent documentation.

Next time, start a NEW thread for your question.