I've been asked to create a Combined Chart with 2 Y axis and 1 line (mscolumn3dlinedy according to the documentation (http://www.fusioncharts.com/dev/chart-attributes.html?chart=mscolumn3dlinedy) that can retrieve data from MySQL and I'm having a hard time doing it. In the FusionCharts docs, there are examples to make charts using MySQL and PHP, but the only example involves a 2d column chart, which has quite a simple structure.
My problem is that in that example (http://www.fusioncharts.com/dev/using-with-server-side-languages/php/creating-charts-with-data-from-a-database.html), they use a while iterator to put the MySQL data into an array and looking at the combined chart I have to make, it should use more arrays, or at least that's what I think. So I don't really know how to do this particular chart.
I have a table with 6 fields: DT (Data time) TP (int value) OPEN_AMT (int value) IN_AMT (null) OUT_AMT (null) END_AMT (int value)
The Chart must look like
The Chart I'm getting is this
It's empty, but I am getting the Data from the DataBase. If you look at the source code of my page you can see that the structure of the chart it's a little messed up, but the data is there. (http://sisfacil.cl/securestart/graficos.php
I've been trying to look at the documentation but I just can't find anything that helps me.
The code of the chart is this
<?php
$stmt = "SELECT * FROM datos_grafico_01
WHERE year(DT)=2016
AND month(DT)=3";
$result = $mysqli->query($stmt) or exit("Error code ({$mysqli->errno}): {$mysqli->error}");
if ($result) {
$arrData = array(
"chart" => array(
"caption" => "Merchandise",
"xAxisname" => "Días de Marzo",
"pYAxisName" => "$",
"sYAxisName" => "$",
"numberPrefix" => "$",
"sNumberSuffix" => "$",
"sYAxisMaxValue" => "6000000000",
"paletteColors" => "#0075c2,#1aaf5d,#f2c500",
"bgColor" => "#ffffff",
"showBorder" => "0",
"showCanvasBorder" => "0",
"usePlotGradientColor" => "0",
"plotBorderAlpha" => "10",
"legendBorderAlpha" => "0",
"legendBgAlpha" => "0",
"legendShadow" => "0",
"showHoverEffect" => "1",
"valueFontColor" => "#ffffff",
"rotateValues" => "1",
"placeValuesInside" => "1",
"divlineColor" => "#999999",
"divLineDashed" => "1",
"divLineDashLen" => "1",
"divLineGapLen" => "1",
"canvasBgColor" => "#ffffff",
"captionFontSize" => "14",
"subcaptionFontSize" => "14",
"subcaptionFontBold" => "0"
)/*
"categories" => array(
"category" => array(
"label" => "2",
"label" => "3",
"label" => "4",
"label" => "5",
"label" => "6",
"label" => "7",
"label" => "8",
"label" => "9",
"label" => "10",
"label" => "11",
"label" => "12",
"label" => "13",
"label" => "14",
"label" => "15",
"label" => "16",
"label" => "17",
"label" => "18",
"label" => "19",
"label" => "20",
"label" => "21",
"label" => "22",
"label" => "23",
"label" => "24",
"label" => "25",
"label" => "26",
"label" => "27",
"label" => "28",
"label" => "29",
"label" => "30",
"label" => "31"
)
)*/
);
$arrData["categories"] = array();
$cat["category"] = array();
//Vaciar los datos de mysql en el arreglo
while($row = mysqli_fetch_array($result)) {
array_push($cat["category"], array(
"label" => $row["DT"]
)
);
}
array_push($arrData["categories"], array(
$cat["category"]
)
);
$arrData["dataset"] = array();
$data1["data"] = array();
$data2["data"] = array();
$data3["data"] = array();
//Vaciar los datos de mysql en el arreglo
while($row = mysqli_fetch_array($result)) {
array_push($data1["data"], array(
"value" => $row["IN_AMT"]
)
);
array_push($data2["data"], array(
"value" => $row["OUT_AMT"]
)
);
array_push($data3["data"], array(
"value" => $row["END_AMT"]
)
);
}
array_push($arrData["dataset"], array(
"seriesname" => "INT_AMT",
$data1["data"],
"seriesname" => "OUT_AMT",
$data2["data"],
"seriesname" => "END_AMT",
"renderAs"=> "line",
"parentYAxis"=> "S",
"showValues"=> "0",
$data3["data"]
)
);
$jsonEncodedData = json_encode($arrData);
$myChart = new FusionCharts("mscolumn3dlinedy", "MySQLChart", 800, 600, "chartContainer", "json", $jsonEncodedData);
$myChart->render();
$mysqli->close();
}
?>
Notice that the first categories part is commented as I'm retrieving it from the DB down below, using arrays.
I'm not particularly an expert in programming so I could use any help, especially with the structure of the chart and the code making of this.
For example, the structure is supposed to look like this in the dataset part:
"dataset": [
{
"seriesname": "Food Products",
"data": [
{
"value": "11000"
},
{
"value": "14000"
},
{
"value": "10500"
},
{
"value": "15000"
}
]
},
{
"seriesname": "Non-Food Products",
"data": [
{
"value": "14400"
},
{
"value": "14800"
},
{
"value": "8300"
},
{
"value": "11800"
}
]
},
{
"seriesname": "Profit %",
"renderAs": "line",
"parentYAxis": "S",
"showValues": "0",
"data": [
{
"value": "14"
},
{
"value": "16"
},
{
"value": "15"
},
{
"value": "17"
}
]
}
But mine, when rendering the chart only shows something like
"dataset":[{"seriesname":"END_AMT","0":[],"1":[],"renderAs":"line","parentYAxis":"S","showValues":"0","2":[]}]}});
});
SO. If this has been a TL;DR text.. and some of you probably ask to simplify my question. it would be:
What's what I'm doing wrong, that when I render my combined chart, the structure gets messed up and doesn't load properly?
If you need some short tips to point out
- I AM retrieving data from MySQL. (see source code)
- I suck at arrays (and probably at programming in general)
- FusionChart documentation doesn't have any multiple/combined chart example for MySQL
- I think the problems are the arrays I made.
EDIT:
I've managed to solve this. I just added the array_value to extract the values on the array_push.
<?php
$stmt = "SELECT DAY(DT) as DT, IN_AMT, OUT_AMT, END_AMT FROM datos_grafico_01 WHERE year(DT)=2016 AND month(DT)=3 ORDER BY DT ASC";
$result = $mysqli->query($stmt) or exit("Error code ({$mysqli->errno}): {$mysqli->error}");
if ($result) {
$arrData = array(
"chart" => array(
"caption" => "Merchandise",
"xAxisname" => "Días de Marzo",
"pYAxisName" => "$",
"sYAxisName" => "$",
"numberPrefix" => "$",
"sNumberSuffix" => "$",
"paletteColors" => "#0075c2,#1aaf5d,#f2c500",
"bgColor" => "#ffffff",
"showBorder" => "0",
"showCanvasBorder" => "0",
"usePlotGradientColor" => "0",
"plotBorderAlpha" => "10",
"legendBorderAlpha" => "0",
"legendBgAlpha" => "0",
"legendShadow" => "0",
"showHoverEffect" => "1",
"valueFontColor" => "#ffffff",
"rotateValues" => "1",
"placeValuesInside" => "1",
"divlineColor" => "#999999",
"divLineDashed" => "1",
"divLineDashLen" => "1",
"divLineGapLen" => "1",
"canvasBgColor" => "#ffffff",
"captionFontSize" => "14",
"subcaptionFontSize" => "14",
"subcaptionFontBold" => "0"
)
);
$arrData["categories"] = array();
$cat["category"] = array();
$arrData["dataset"] = array();
$data1["data"] = array();
$data2["data"] = array();
$data3["data"] = array();
//Vaciar los datos de mysql en el arreglo
while($row = mysqli_fetch_array($result)) {
array_push($cat["category"], array(
"label" => $row["DT"]
)
);
array_push($data1["data"], array(
"value" => $row["IN_AMT"]
)
);
array_push($data2["data"], array(
"value" => $row["OUT_AMT"]
)
);
array_push($data3["data"], array(
"value" => $row["END_AMT"]
)
);
}
array_push($arrData["categories"], array(
"category" => array_values($cat["category"])
)
);
array_push($arrData["dataset"], array(
"seriesname" => "IN_AMT",
"data" => array_values($data1["data"])
)
);
array_push($arrData["dataset"], array(
"seriesname" => "OUT_AMT",
"data" => array_values($data2["data"])
)
);
array_push($arrData["dataset"], array(
"seriesname" => "END_AMT",
"renderAs"=> "line",
"parentYAxis"=> "S",
"showValues"=> "0",
"data" => array_values($data3["data"])
)
);
$jsonEncodedData = json_encode($arrData);
$myChart = new FusionCharts("mscolumn3dlinedy", "MySQLChart", 1200, 1000, "chartContainer", "json", $jsonEncodedData);
$myChart->render();
$mysqli->close();
}
?>
It worked. Plus I had to change the order of the code and simplify it as I had more than 1 while. The Chart looks bad anyway, but that's because the data from the tables are a mess.
