Help with pie charts please

[MikeG]

Hi

I am extreamly rusty with MySQL and PHP (doing some work to knock the rust
off!) but I am having a nightmare with creating a pie chart from some data
pulled from a MySQL Db.

the code I am using is here-

<Code>

mysql_select_db("firstre_FRSystems", $con);

$result = mysql_query("SELECT Count(TblCBTFeedback.FdCBTFBId) AS Unittype,
TblCBTFeedback.FdCBTFBAns22 FROM TblCBTFeedback WHERE
TblCBTFeedback.FdCBTFBAns21 = 'JIO Feedback' GROUP BY
TblCBTFeedback.FdCBTFBAns22 ORDER BY TblCBTFeedback.FdCBTFBAns22 ASC");

$data = array();

$i=0;

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

$unit1 = $row;

$unit2 = $row;

$data = array("",$unit1,$unit2);

$i++;

}

// print_r($data);

$plot = new PHPlot(200,200);

$plot->SetImageBorderType('plain');

$plot->SetPlotType('pie');

$plot->SetDataType('text-data-single');

$plot->SetDataValues($unit2);

Set enough different colors;

$plot->SetDataColors(array('red', 'green', 'blue', 'yellow', 'cyan',

'magenta', 'brown', 'lavender', 'pink',

'gray', 'orange'));

Main plot title:

$plot->SetTitle("Unit Types");

Build a legend from our data array.

Each call to SetLegend makes one line as "label: value".

foreach ($data as $row)

$plot->SetLegend(implode(': ', $row));

$plot->SetShading(0);

$plot->SetLabelScalePosition(0.2);

$plot->DrawGraph();

Please help! Where am I going wrong? The problem I thnk is with the array and
how it is creating the pie chart. All works fine with a static array but as
soon as I try to dynamically generate it all goes wrong. I have spent the last
two hours searching the forum and now my brain is melting! Any assistance will
be appreciated.

Thanks in advance.

[lbayuk]

Before looking at your script: It is always a good idea to have a way to
display the data array without producing a plot. If there is no security risk,
I have sometimes added a parameter "debug" to a script, which makes it build
the data array then do print_r() on it and exit. Then adding ?debug=1 to the
URL shows the data array (not an image) and is very useful for debugging.

The first thing I see in your script is SetDataValues($unit2). But $unit2 is
not an array - it seems to be a temporary variable that you use for each row
fetched from the database. You definitely need an array here. But what goes in
the array is hard for me to tell. You have 2 columns coming from your database
- a count and a value of some kind - and I'm not sure what you are trying to
represent in your pie chart. Pie charts can only show a single data set.

With SetDataType('text-data-single'), your data array will look like this:
array(array('', value1), array('', value2), ...)

That is: a series of rows, each row is an array with an empty label and a
value. Each value makes 1 pie segment.

By the way, major changes are coming in the next release for pie charts. There
will be a lot of new options for labels (currently PHPlot can only show
segment percentages). Also it will be smarter about calculating the pie size
and label position.

Hope this helps. Please follow up, especially if you fix up the data array and
it still doesn't work.

[MikeG]

Thanks for the pointers. The query does pull back two data elements, Unit1 is
the label and unit2 is the value (it provides a count of the number of entries
in the Db against unit1).

I am having real problems creating the correct syntax for the array. In the
example simpleplot.html the pie chart shows the values in the pie chart and a
legend with the respective data values. this is exactly what I am hoping to
achieve, with unit1 is the value for the legend and unit2 is the data for the
pie chart, but I just cannot work out how to produce the array in the correct
format to achieve this?

I am very interested in the future release as displaying the value and the
percentage will be excellent.

In the mean time if you could provide me with the correct method of producing
the array I would be very grateful.

[MikeG]

Sorry I have been looking at your advice and I am afraid that it has confussed
me even more?

In the example simpleplot.html the array contains the labels and the values.
There does not appear to be any other array called so how does that example
produce the pie chart if you are stating that the array should contain no
labels only the data? Sorry am I just being really thick?

Your assistance is appreciated.

[lbayuk]

By simpleplot, I assume you mean the little script inside the README.txt that
comes with PHPlot. There isn't much to that one - it was meant to be the
shortest possible script that could show somebody that PHPlot works after
installing. It probably won't help much with anything else. (It makes a line
plot, not a pie chart by the way.)

There are more complete examples in the PHPlot Reference Manual, which you can
download from this Sourceforge site under Files, or you can view it online at
http://phplot.sourceforge.net/ (there is
also a PDF version linked from that page).

Here is a link to the manual section which describes data arrays for pie
charts:

http://phplot.sourceforge.net/phplotdocs/conc-plottypes.html#plottype-
pie

It also has links there to the examples.

Back to your script. I meant to say that $unit1 and $unit2 only hold the
values from the last database query result row, because they are overwritten
in each loop iteration. Your $data array is much closer to what a data array
for PHPlot looks like - in fact it would work fine for a line plot with 2
lines (2 data sets). It won't do for a pie chart, because it has 2 values for
row.

You can put labels in the data array if you want, but PHPlot currently ignores
them with pie charts. (In the next release, you will be able to use them to
label the pie segments.)

Perhaps you can comment-out the database query code and loop, and start with a
data array assigned constant values. See if you get the plot you want. Then
you can re-add the database query code and build the array that way. You might
start with something like:

$data = array(array('', 1), array('', 2), array('', 1));
...
$plot->SetDataValues($data);

This should give you a pie chart with 3 sectors, 25%, 50%, and 25%.

[MikeG]

The example that i am using demonstrates using an array like this:

PHPlot Example: Pie/text-data-single

require_once 'phplot.php';

The data labels aren't used directly by PHPlot. They are here for our

reference, and we copy them to the legend below.

$data = array(

array('Army', 5),

array('Royal Navy', 1),

array('Royal Marines', 2),

array('RAF', 4),

array('Other', 2),

);

print_r($data);

$plot = new PHPlot(400,400);

$plot->SetImageBorderType('plain');

$plot->SetPlotType('pie');

$plot->SetDataType('text-data-single');

$plot->SetDataValues($data);

Set enough different colors;

$plot->SetDataColors(array('brown','navy','DimGrey', 'grey', 'ivory',
'snow'));

Main plot title:

$plot->SetTitle("Unit Types");

Build a legend from our data array.

Each call to SetLegend makes one line as "label: value".

foreach ($data as $row)

$plot->SetLegend(implode(': ', $row));

$plot->SetShading(0);

$plot->SetLabelScalePosition(0.3);

$plot->DrawGraph();

?>

This is what I need to reproduce, a live example can be seen at http://www
.first-response.org.uk/simpleplot.html

What I am trying to understand is that the array in the above code is
different than the array you are sugesting and different from the
documentation. In the array above it shows the label and the value. It uses
the value for the pie cahrt and then the label and the value for the legend.
Iam trying to create the array from the MySQL database to replace the array in
the above code, however every permitation that I have tried with the
dynamically coded array fails. I am simply looking for the correct syntax for
the creation of the array to replace the array shown in the code above with a
dynamically created array from the database that will automatically update as
data is added.

Please help me to create the correct array dynamically as using the demo on
the linked page is what I am looking for.

[lbayuk]

I'm still not sure I get it, but let's see if this is right. Your query is
returning rows with 2 columns: a count, and a type. I think the type acts as a
label here. Is this correct? So the count is the number of items or events of
that type, and the pie chart will show one slice per type.

If I'm on the right track, I would do something like this. First, change the
query so it returns the type in the first column, and count in the second, if
possible. This makes it easier to build the data array, like this:

// Query returns 2 columns, a type, and a count (in that order - important)
$result = mysql_query("SELECT ..."); 
// Check query status here!
$data = array(); 
while ($row = mysql_fetch_row($result)) $data[] = $row;
...
$plot->SetDataValues($data);

So now you have a data array with each row having 2 entries: label and value.
With PHPlot data type text-data-single, PHPlot will use each row for one pie
slice. The values will indicate the slice sizes (as a portion of the sum of
all values). The labels won't be used directly, but you can use them to build
a legend as in the sample.

Note I'm using mysql_fetch_row(), which returns a numeric-indexed array. If
you use mysql_fetch_array(), you need to either include the MYSQL_NUM flag so
you only get numeric indexes, or else you need to copy the fields one by one
from the result row into the data array. Using mysql_fetch_array() without the
flag results in double entries for the query results, which won't work
directly with PHPlot.

[MikeG]

Ibayuk thats it!

Thank you, Thank you. That was the problem, I needed to use mysql_fetch_row
and make the query the correct way round. Brilliant thank you again.

When is the new release going to be available I am very interested in
displaying values as well as percentages in the graph?

But thanks again.

[lbayuk]

Good, I'm glad it is working.

I was hoping the new release would be done this year (meaning today or
tomorrow), and it is still possible. It is a very large change, but most of
the hard stuff is done.