Stacked Chart, Newbie, Lost

[kiwi1066]

Hi

 

I am looking at using FusionCharts to see how easy it is. I have worked out how to do some basic charts but now attempting to get a 3D Stacked chart working.

I have a small amout of php understanding so was hoping someone might have a look at what i have below end let me know where I am going wrong.

 

What I have is a sql query that produce this output (see attachment)

 

This is the php page

 

 

<?php

//In this example, we show how to connect FusionCharts to a database.

//For the sake of ease, we've used an MySQL databases containing two

//tables.

// Connect to the DB

$link = connectToDB();

// SQL query for category labels

$strQueryCategories = "SELECT

countries.countries_name

FROM

customers

INNER JOIN address_book ON (customers.customers_id = address_book.customers_id)

INNER JOIN countries ON (address_book.entry_country_id = countries.countries_id)

INNER JOIN hydg_activity ON (customers.customers_nick = hydg_activity.NICK)

GROUP BY

countries.countries_name";

// Query database

$resultCategories = mysql_query($strQueryCategories) or die(mysql_error());

// SQL query for factory output data

$strQueryData = "SELECT

AUX.COUNTRIES_NAME,

SUM(ONE) AS ONE,

SUM(TWO) AS TWO,

SUM(THREE) AS THREE,

SUM(FOUR) AS FOUR,

SUM(FIVE) AS FIVE

FROM (

 

SELECT

CT.COUNTRIES_NAME,

CASE

WHEN ACT.ACTIVITYID >= 10 AND ACT.ACTIVITYID <= 19 THEN 1

ELSE 0

END AS ONE,

CASE

WHEN ACT.ACTIVITYID >= 20 AND ACT.ACTIVITYID <= 29 THEN 1

ELSE 0

END AS TWO,

CASE

WHEN ACT.ACTIVITYID >= 30 AND ACT.ACTIVITYID <= 39 THEN 1

ELSE 0

END AS THREE,

CASE

WHEN ACT.ACTIVITYID >= 40 AND ACT.ACTIVITYID <= 49 THEN 1

ELSE 0

END AS FOUR,

CASE

WHEN ACT.ACTIVITYID >= 50 AND ACT.ACTIVITYID <= 59 THEN 1

ELSE 0

END AS FIVE

FROM HYDG_ACTIVITY ACT

LEFT JOIN CUSTOMERS PL

ON ACT.NICK = PL.CUSTOMERS_NICK

LEFT JOIN ADDRESS_BOOK AD

ON PL.CUSTOMERS_ID = AD.CUSTOMERS_ID

LEFT JOIN COUNTRIES CT

ON AD.ENTRY_COUNTRY_ID = CT.COUNTRIES_ID

) AUX

GROUP BY AUX.COUNTRIES_NAME";

// Query database

$resultData = mysql_query($strQueryData) or die(mysql_error());

//We also keep a flag to specify whether we've to animate the chart or not.

//If the user is viewing the detailed chart and comes back to this page, he shouldn't

//see the animation again.

//$strXML will be used to store the entire XML document generated

//Generate the chart element

$strXML = "<chart legendPostion='' caption='HYDG Country stuff' subCaption='By Country' xAxisName='Country' yAxisName='Number' showValues='0' formatNumberScale='0' rotateValues='1' animation='1'>";

// Build category XML

$strXML .= buildCategories ($resultCategories, "countries_name");

// Build datasets XML

$strXML .= buildDatasets ( $resultData, "(don't know", "Don't know" );

//Finally, close <chart> element

$strXML .= "</chart>";

//Create the chart - Pie 3D Chart with data from strXML

echo renderChart("charts/FusionCharts/StackedColumn3D.swf", "", $strXML, "COUNTRIES_NAME", 500, 300, true, false);

// Free database resource

mysql_free_result($resultCategories);

mysql_free_result($resultData);

mysql_close($link);

 

/***********************************************************************************************

* Function to build XML for categories

* @param $result Database resource

* @param $labelField Field name as String that contains value for chart category labels

*

* @return categories XML node

*/

function buildCategories ( $result, $labelField )

{

$strXML = "";

if ($result) {

$strXML = "<categories>";

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

$strXML .= "<category label='" . $ors[$labelField]. "'/>";

}

$strXML .= "</categories>";

}

return $strXML;

}

 

/***********************************************************************************************

* Function to build XML for datesets that would contain chart data

* @param $result Database resource. The data should come ordered by a control break

field which would require to identify datasets and set its value to

dataset's series name

* @param $valueField Field name as String that contains value for chart dataplots

* @param $controlBreak Field name as String that contains value for chart dataplots

*

* @return Dataset XML node

*/

function buildDatasets ( $result, $valueField, $controlBreak )

{

$strXML = "";

if ($result) {

$controlBreakValue ="";

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

if( $controlBreakValue != $ors[$controlBreak] )

{

$controlBreakValue = $ors[$controlBreak];

$strXML .= ( $strXML =="" ? "" : "</dataset>") . ( "<dataset seriesName='" . $controlBreakValue . "'>" ) ;

}

$strXML .= "<set value='" . $ors[$valueField] . "'/>";

}

$strXML .= "</dataset>";

}

return $strXML;

}

?>

 

 

 

At this point $strXML .= buildDatasets ( $resultData, "(don't know", "Don't know" );

I am lost.

 

If someone would be kind enough to let me know how to return a stacked chart that in this case has two columns for the country with the 5 values stacked in it, I thankyou in advance

[Guest Basundhara Ghosal]

Hi,

 

Welcome to FusionCharts forum.

 

Could you please obtain the whole XML codes passed in "strXML" in a textbox and then send us the same?

 

Awaiting your reply.

[kiwi1066]

Hi

 

I am not sure what you are asking. I don't know how to obtain the whole XML codes passed in "strXML"

Are you able to give me some advise on how I do that.

 

Thanks

[Rahul Kumar]

Hi,

 

Could you please put mysql_data_seek($result,0); to rewind the query result in each function buildCategories and buildDatasets?

e.g.:

 

/***********************************************************************************************
* Function to build XML for categories
* @param $result Database resource
* @param $labelField Field name as String that contains value for chart category labels
* 
* @return categories XML node 
*/
function buildCategories ( $result, $labelField )
{
$strXML = "";
if ($result) {
$strXML = "<categories>";
mysql_data_seek($result,0);
while($ors = mysql_fetch_array($result)) {
$strXML .= "<category label='" . $ors[$labelField]. "'/>";
}
$strXML .= "</categories>";
}
return $strXML;
}

/***********************************************************************************************
* Function to build XML for datesets that would contain chart data
* @param $result Database resource. The data should come ordered by a control break 
field which would require to identify datasets and set its value to 
dataset's series name
* @param $valueField Field name as String that contains value for chart dataplots
* @param $controlBreak Field name as String that contains value for chart dataplots
* 
* @return Dataset XML node 
*/
function buildDatasets ( $result, $valueField, $controlBreak )
{
$strXML = "";
if ($result) {
$controlBreakValue ="";
mysql_data_seek($result,0);
while( $ors = mysql_fetch_array($result) ) {
if( $controlBreakValue != $ors[$controlBreak] ) 
{
$controlBreakValue = $ors[$controlBreak];
$strXML .= ( $strXML =="" ? "" : "</dataset>") . ( "<dataset seriesName='" . $controlBreakValue . "'>" ) ;
}
$strXML .= "<set value='" . $ors[$valueField] . "'/>";
}
$strXML .= "</dataset>";
}
return $strXML;
}