Как создать комбинированную диаграмму, используя диаграммы слияния с данными, поступающими из базы данных
У меня есть следующий код, который генерирует столбчатую диаграмму, используя 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
Revenue"},
{
"startValue": "21",
"parentYAxis": "s",
"color": "#f2c500",
"displayvalue": "Average
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
Revenue"));
array_push($line, array("startValue"=>"21","parentYAxis"=>"s","color"=>"#f2c500","displayvalue"=>"Average
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');
Другие решения
Других решений пока нет …