RE: problem with example that you have given in FusionCharts Blueprint Application - PHP + MySQL version

Sudipto Choudhury — Fri, 25 Apr 2008 13:09:17 GMT

Hi Mahesh,

Thanks for pointing this out. We modify the code in a different fashion up here. We get all available month names separately and also keep a track of the available months in an array for refrence while creating datasets. Note that now we order the SQL.

Could to please try the following code?

Here is the modified function :

//getCumulativeSalesByCatXML returns the cumulative sales for each category
//in a given year
function getCumulativeSalesByCatXML($intYear, $forDataURL) {

// Function to connect to the DB
$link = connectToDB();

//To store categories - also flag to check whether category is
//already generated

//Initialize XML elements
//Categories
$strCat = "<categories>";
$arrCat =array();
$strSQL = "SELECT distinct Month(o.OrderDate) as MonthNum from FC_Orders as o WHERE year(o.OrderDate)=$intYear order by Month(o.OrderDate)";
$result = mysql_query($strSQL) or die(mysql_error());
if ($result) {
$mc=0;
while($orsCat = mysql_fetch_array($result)) {
//Add this category as dataset
$arrCat[$mc++]=date("n",mktime(0,0,0,$orsCat['MonthNum'],2,1994));
$strCat .= "<category label='" . date("F",mktime(0,0,0,$orsCat['MonthNum'],2,1994)) . "' />";
}
mysql_free_result($result);

}
$strCat .= "</categories>";

//First we need to get unique categories in the database
$strSQL = "Select CategoryID,CategoryName from FC_Categories GROUP BY CategoryID,CategoryName";
$result = mysql_query($strSQL) or die(mysql_error());

//To store datasets and sets
$strDataXML = "";

if ($result) {
while($orsCat = mysql_fetch_array($result)) {
//Add this category as dataset
$strDataXML .= "<dataset seriesName='" . escapeXML($orsCat['CategoryName'],$forDataURL) . "'>";
//Now, we need to get monthly sales data for products in this category
$strSQL = "SELECT Month(o.OrderDate) as MonthNum, g.CategoryID, g.CategoryName, ROUND(SUM(d.Quantity),0) as Quantity, SUM(d.Quantity*p.UnitPrice) As Total FROM FC_Categories as g, FC_Products as p, FC_Orders as o, FC_OrderDetails as d WHERE year(o.OrderDate)=" . $intYear ." and g.CategoryID=" . $orsCat['CategoryID'] . " and d.ProductID=p.ProductId and g.CategoryID= p.CategoryID and o.OrderID= d.OrderID GROUP BY g.CategoryID,g.CategoryName,Month(o.OrderDate) order by Month(o.OrderDate)";
//Execute it
$result2 = mysql_query($strSQL) or die(mysql_error());
$mc=0;
while($ors = mysql_fetch_array($result2)) {
//Append <category label=''> if not already done
//Generate the link
$strLink = urlencode("updateProductChart(" . $intYear . "," . $ors['MonthNum'] . "," . $ors['CategoryID'] . ");");

while($arrCat[$mc++]<$ors["MonthNum"]){
$strDataXML .="<set/>";
}
$strDataXML .= "<set value='" . $ors['Total'] . "' link='" . $strLink . "'/>";
}
//Clear up objects
mysql_free_result($result2);
//Close dataset element
$strDataXML .= "</dataset>";
}
}
mysql_close($link);

//Create full XML
$strXML = $strCat . $strDataXML;

//Return
return $strXML;
}

problem with example that you have given in FusionCharts Blueprint Application - PHP + MySQL version

maheshshinde9 — Fri, 25 Apr 2008 03:37:18 GMT

got some problem with example you have given free php & mysql sample.(FusionCharts Blueprint Application - PHP + MySQL version)

This error contains in sales by category page (i.e. multiseries example).(pages that contains data to show multiseries charts are Data_SalesByCategory.php,Default.php & DataGen.php)

I have changed tables fc_orders, fc_orderdetails & fc_products as follows.

I have changed orderdate to 2/5/1994 for orderid 10249 in fc_orders table. In fc_orderdetails table orderid 10249 contains two productid 14 & 51. In fc_products table , productid 14's category id is 7 & productid 51's category id is 7.

Means bar chart should show Feb(means second month) data. But it does not show that data.

Because you have set flag $catXMLDone for only first product 'Beverages'. Can you find the solution

FusionCharts Forum / FusionCharts Free / FusionCharts Free and PHP / problem with example that you have given in FusionCharts Blueprint Application - PHP + MySQL version / Latest Posts

RE: problem with example that you have given in FusionCharts Blueprint Application - PHP + MySQL version

problem with example that you have given in FusionCharts Blueprint Application - PHP + MySQL version