我有下面的代码，它使用fusioncharts生成柱状图。但是，当我尝试生成json来生成组合图时，它似乎不起作用。请建议我必须对代码进行哪些更改。
<?php
$serverName = "forestroot"; //serverName'instanceName
$connectionInfo = array( "Database"=>"********", "UID"=>"*****", "PWD"=>"*******");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
//echo "Connection established.<br />";
}else{
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
//Query to bind values from database into the dropdown list
//echo $_GET['storetype'];
//echo $_GET['month'];
if ($_GET['num'] == "all"){
$sql = "SELECT * FROM top_odi_wicket_takers";
}
else{
$sql = "SELECT TOP 5
[player]
,[wickets]
FROM [PristineDigit].[dbo].[top_odi_wicket_takers]";
}
//echo $sql;
$params = array();
$options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$result = sqlsrv_query( $conn, $sql, $params, $options);
//initialize the array to store the processed data
$jsonArray = array();
//check if there is any data returned by the SQL Query
if (sqlsrv_num_rows( $result ) > 0) {
//Converting the results into an associative array
while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
$jsonArrayItem = array();
$jsonArrayItem['label'] = $row['player'];
$jsonArrayItem['value'] = $row['wickets'];
//append the above created object into the main array.
array_push($jsonArray, $jsonArrayItem);
}
}
sqlsrv_close( $conn);
header('Content-type: application/json');
echo json_encode($jsonArray);
?>
组合图的json看起来是这样的，我应该如何使用php生成它？
{
"chart": {
"caption": "Revenues and Profits",
"subCaption": "For last year",
"xAxisname": "Month",
"pYAxisName": "Amount (In USD)",
"sYAxisName": "Profit %",
"numberPrefix": "$",
"sNumberSuffix": "%",
"sYAxisMaxValue": "50",
"numDivLines": "3",
"theme": "fint"
},
"categories": [
{
"category": [
{
"label": "Jan"
},
{
"label": "Feb"
},
{
"label": "Mar"
},
{
"label": "Apr"
},
{
"label": "May"
},
{
"label": "Jun"
},
{
"label": "Jul"
},
{
"label": "Aug"
},
{
"label": "Sep"
},
{
"label": "Oct"
},
{
"label": "Nov"
},
{
"label": "Dec"
}
]
}
],
"dataset": [
{
"seriesName": "Revenues",
"data": [
{
"value": "16000"
},
{
"value": "20000"
},
{
"value": "18000"
},
{
"value": "19000"
},
{
"value": "15000"
},
{
"value": "21000"
},
{
"value": "16000"
},
{
"value": "20000"
},
{
"value": "17000"
},
{
"value": "22000"
},
{
"value": "19000"
},
{
"value": "23000"
}
]
},
{
"seriesName": "Profits",
"renderAs": "area",
"showValues": "0",
"data": [
{
"value": "4000"
},
{
"value": "5000"
},
{
"value": "3000"
},
{
"value": "4000"
},
{
"value": "1000"
},
{
"value": "7000"
},
{
"value": "1000"
},
{
"value": "4000"
},
{
"value": "1000"
},
{
"value": "8000"
},
{
"value": "2000"
},
{
"value": "7000"
}
]
},
{
"seriesName": "Profit %",
"parentYAxis": "S",
"renderAs": "line",
"showValues": "0",
"data": [
{
"value": "25"
},
{
"value": "25"
},
{
"value": "16.66"
},
{
"value": "21.05"
},
{
"value": "6.66"
},
{
"value": "33.33"
},
{
"value": "6.25"
},
{
"value": "25"
},
{
"value": "5.88"
},
{
"value": "36.36"
},
{
"value": "10.52"
},
{
"value": "30.43"
}
]
}
],
"trendlines": [
{
"line": [
{
"startValue": "18833",
"color": "#0075c2",
"valuePadding": "20",
"displayvalue": "Average{br}Revenue"
},
{
"startValue": "21",
"parentYAxis": "s",
"color": "#f2c500",
"displayvalue": "Average{br}Profit %"
}
]
}
]
}
FusionCharts PHP包装器，使您可以在PHP网站中创建交互式数据驱动图表，而无需编写任何JavaScript代码。
<?php
/* Include the `fusioncharts.php` file that contains functions to embed the charts. */
include("fusioncharts.php");
/* The following 4 code lines contain the database connection information. Alternatively, you can
move these code lines to a separate file and include the file here. You can also modify
this code based on your database connection. */
$hostdb = "localhost:3306"; // MySQl host
$userdb = "root"; // MySQL username
$passdb = ""; // MySQL password
$namedb = "test"; // MySQL database name
// Establish a connection to the database
$dbhandle = mysqli_connect($hostdb, $userdb, $passdb, $namedb);
/*Render an error message, to avoid abrupt failure, if the database connection parameters are incorrect */
if (!$dbhandle) {
exit("There was an error with your connection: ".mysqli_connect_error());
}
?>
<html>
<head>
<title>FusionCharts XT - Column 2D Chart - Data from a database</title>
<script type="text/javascript" src="http://static.fusioncharts.com/code/latest/fusioncharts.js"></script>
<script type="text/javascript" src="http://static.fusioncharts.com/code/latest/themes/fusioncharts.theme.fint.js?cacheBust=56"></script>
</head>
<body>
<?php
// Form the SQL query that returns the top 10 most populous countries
$strQuery = "SELECT * FROM `MOCK_DATA` ORDER BY `MOCK_DATA`.`id` ASC";
// Execute the query, or else return the error message.
$result = mysqli_query($dbhandle,$strQuery);
// If the query returns a valid response, prepare the JSON string
if ($result) {
// The `$arrData` array holds the chart attributes and data
$arrData = array(
"chart" => array(
"caption"=>"Revenues and Profits",
"subCaption"=>"For last year",
"xAxisname"=>"Month",
"pYAxisName"=>"Amount (In USD)",
"sYAxisName"=>"Profit %",
"numberPrefix"=>"$",
"sNumberSuffix"=>"%",
"sYAxisMaxValue"=>"50",
"numDivLines"=>"3",
"theme"=>"fint"
)
);
//prepare categories
$arrData["categories"] = array();
$category = array();
// Push the data into the category array
while($row = mysqli_fetch_array($result)) {
array_push($category, array(
"label" => $row["MONTHS"]
)
);
}
array_push($arrData["categories"], array("category" => $category));
//prepare dataset
$arrData["dataset"] = array();
array_push($arrData["dataset"], buildDataset(array("seriesName"=>"REVENUES"), "REVENUES", $strQuery));
array_push($arrData["dataset"], buildDataset(array("seriesName"=>"PROFITS","renderAs"=>"area",
"showValues"=>"0",), "PROFITS", $strQuery));
array_push($arrData["dataset"], buildDataset(array("seriesName"=>"PROFIT %", "parentYAxis"=>"S",
"renderAs"=>"line",
"showValues"=>"0"), "PROFIT_IN_PERCENTAGE", $strQuery));
//prepare trendline
$arrData["trendlines"] = array();
$line = array();
array_push($line, array("startValue"=>"18833","color"=>"#0075c2","valuePadding"=>"20", "displayvalue"=>"Average{br}Revenue"));
array_push($line, array("startValue"=>"21","parentYAxis"=>"s","color"=>"#f2c500","displayvalue"=>"Average{br}Profit %"));
array_push($arrData["trendlines"], array("line" => $line));
//JSON Encode the data to retrieve the string containing the JSON representation of the data in the array.
$jsonEncodedData = json_encode($arrData);
/*Create an object for the mscombi chart using the FusionCharts PHP class constructor. Syntax for the constructor is ` FusionCharts("type of chart", "unique chart id", width of the chart, height of the chart, "div id to render the chart", "data format", "data source")`. Because we are using JSON data to render the chart, the data format will be `json`. The variable `$jsonEncodeData` holds all the JSON data for the chart, and will be passed as the value for the data source parameter of the constructor.*/
$mscombidy2dChart = new FusionCharts("mscombidy2d", "myFirstChart" , 600, 300, "chart-1", "json", $jsonEncodedData);
// Render the chart
$mscombidy2dChart->render();
// Close the database connection
$dbhandle->close();
}
function buildDataset($data, $dataColumName, $sqlquery) {
$resultset = mysqli_query($GLOBALS['dbhandle'], $sqlquery);
$datasetinner = $data;
$makedata = array();
while($row = mysqli_fetch_array($resultset)) {
array_push($makedata, array(
"value" => $row[$dataColumName]
));
}
$datasetinner["data"] = $makedata;
return $datasetinner;
}
?>
<div id="chart-1"><!-- Fusion Charts will render here--></div>
</body>
</html>
这是SQL语句
CREATE TABLE `MOCK_DATA` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`MONTHS` varchar(50) DEFAULT NULL,
`REVENUES` int(11) DEFAULT NULL,
`PROFITS` int(11) DEFAULT NULL,
`PROFIT_IN_PERCENTAGE` decimal(3,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `MOCK_DATA` (`id`, `MONTHS`, `REVENUES`, `PROFITS`, `PROFIT_IN_PERCENTAGE`) VALUES
(1, 'Jan', 19405, 3741, '33.3'),
(2, 'Feb', 22394, 4975, '33.3'),
(3, 'Mar', 18967, 2256, '6.5'),
(4, 'Apr', 22004, 5393, '6.4'),
(5, 'May', 22082, 7392, '34.7'),
(6, 'Jun', 22588, 3121, '22.2'),
(7, 'July', 22708, 2385, '9.4'),
(8, 'Aug', 21389, 5110, '13.0'),
(9, 'Sept', 19451, 5618, '30.0'),
(10, 'Oct', 16247, 6467, '33.0'),
(11, 'Nov', 22209, 2653, '16.0'),
(12, 'Dec', 22438, 2445, '29.0');