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
