I am trying to create an msline chart using PHP and JSON to display data from my SQL database using the Fusion Charts Factory Sample database as my guide.
In both cases (with Factory Sample data and my own data) the first value in each data series are duplicated as the first and second data point, and the last value does not appear on the chart.
My SQL query returns the following datasets
$strQueryCategories
Game
R9,2015
R9,2015
R20,2015
R20,2015
EF,2015
R1,2016
R1,2016
$strQueryData
Team Game Total
Claremont R9,2015 60
Claremont R20,2015 60
Claremont EF,2015 61
Claremont R1,2016 110
East Perth R9,2015 21
East Perth R20,2015 70
East Perth EF,2015 93
East Perth R1,2016 34
// Form the SQL query
$strQueryCategories = "SELECT CONCAT(RoundNo, ',',Season) AS Game FROM MatchDetails WHERE (Team = 'East Perth' OR Opponent = 'East Perth') AND (Team = 'Claremont' OR Opponent = 'Claremont') AND Season > 2014 ORDER BY GameID ASC";
$resultCategories = $dbhandle->query($strQueryCategories)or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");
$strQueryData = "SELECT Team, CONCAT(RoundNo, ',',Season) AS Game, Total FROM MatchDetails WHERE (Team = 'East Perth' OR Opponent = 'East Perth') AND (Team = 'Claremont' OR Opponent = 'Claremont') AND Season >2014 ORDER BY Team, GameId ASC";
$resultData = $dbhandle->query($strQueryData)or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");
if ($resultData) {
$arrData = array( "chart" => array( "caption" => "Head to Head Since Start 2015", "subcaption" => "Scores For & Against", "theme" => "fint" ) );
$arrData["categories"] = array(array("category" => array()));
if ($resultCategories) { $controlBreakValue = ""; while ($row = mysqli_fetch_array($resultCategories)) { if ($controlBreakValue != $row["Game"]) { $controlBreakValue = $row["Game"]; array_push( $arrData["categories"][0]["category"], array("label" => $controlBreakValue)); $controlBreakValue == ""; } } }
$arrData["dataset"] = array(); $i = 0; if ($resultData) { $controlBreakValue = ""; while ($row = mysqli_fetch_array($resultData)) { if ($controlBreakValue != $row["Team"]) { $controlBreakValue = $row["Team"]; array_push($arrData["dataset"], array("seriesname" => $controlBreakValue, "data" => array(array("value" => $row["Total"])))); $controlBreakValue == ""; $i++; } array_push($arrData["dataset"][$i-1]["data"], array("value" => $row["Total"])); } } }
$jsonEncodedData = json_encode($arrData);
$dbhandle->close();
echo $jsonEncodedData;
?>
<html>
<body> <?php
$columnChart = new FusionCharts("msline", "myFirstChart" , 850, 300, "chart-1", "json", $jsonEncodedData);
$columnChart->render();
?>
<div id="chart-1"><!-- Fusion Charts will render here--></div>
</body>
</html>
Chart output is attached. You can see the first value is duplicated.