Ashesh Das
Reputation: 365
How to generate a combination chart using fusioncharts with data coming from the database
I have the following code that generates a column chart using fusioncharts. But when I try to generate the json to produce a combination chart, it does not seem to work. Please suggest that what changes I must bring to my code.
<?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);
?>
The json for a combination chart looks like this, how shall I generate this using 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 %"
}
]
}
]
}
Upvotes: 0
Views: 1332
Answers (2)
Aman Saraswat
Reputation: 172
You can try with this sample code -
<?php
//address of the server where db is installed
$servername = "localhost";
//username to connect to the db
//the default value is root
$username = "root";
//password to connect to the db
//this is the value you specified during installation of WAMP stack
$password = "password";
//name of the db under which the table is created
$dbName = "test";
//establishing the connection to the db.
$conn = new mysqli($servername, $username, $password, $dbName);
//checking if there were any error during the last connection attempt
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
Combination chart sample -
<?php
include("includes/fusioncharts.php");
$columnChart = new FusionCharts("stackedcolumn2dline", "ex1", "100%", 400, "chart-1", "json", '{
"chart": {
"showvalues": "0",
"caption": "Apple's Revenue & Profit",
"subcaption": "(2013-2016)",
"numberprefix": "$",
"numbersuffix": "B",
"plottooltext": "Sales of $seriesName in $label was <b>$dataValue</b>",
"showhovereffect": "1",
"yaxisname": "$ (In billions)",
"showsum": "1",
"theme": "candy"
},
"categories": [
{
"category": [
{
"label": "2013"
},
{
"label": "2014"
},
{
"label": "2015"
},
{
"label": "2016"
}
]
}
],
"dataset": [
{
"seriesname": "iPhone",
"data": [
{
"value": "21"
},
{
"value": "24"
},
{
"value": "27"
},
{
"value": "30"
}
]
},
{
"seriesname": "iPad",
"data": [
{
"value": "8"
},
{
"value": "10"
},
{
"value": "11"
},
{
"value": "12"
}
]
},
{
"seriesname": "Macbooks",
"data": [
{
"value": "2"
},
{
"value": "4"
},
{
"value": "5"
},
{
"value": "5.5"
}
]
},
{
"seriesname": "Others",
"data": [
{
"value": "2"
},
{
"value": "4"
},
{
"value": "9"
},
{
"value": "11"
}
]
},
{
"seriesname": "Profit",
"plottooltext": "Total profit in $label was <b>$dataValue</b>",
"renderas": "Line",
"data": [
{
"value": "17"
},
{
"value": "19"
},
{
"value": "13"
},
{
"value": "18"
}
]
}
]
}');
$columnChart->render();
?>
Document reference - https://www.fusioncharts.com/dev/using-with-server-side-languages/tutorials/php-mysql-charts
https://www.fusioncharts.com/dev/getting-started/php/your-first-chart-using-php
Upvotes: 0
suvradip Saha
Reputation: 71
FusionCharts PHP wrapper that lets you create interactive, data-driven charts in your PHP website without writing any JavaScript code.
<?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>
Here is the SQL statement
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');
Upvotes: 1
Related Questions
- How can I remove a package from Laravel using PHP Composer?
- How to find the last day of the month from date?
- How do I get a YouTube video thumbnail from the YouTube API?
- data from database to fusioncharts in laravel
- How to calculate the difference between two dates using PHP?
- How do I pass variables and data from PHP to JavaScript?
- How to create a FusionCharts Multi-series chart from Database (SQL) using php
- responsive pie chart with fusioncharts
- How to create a combo chart (bar and line) with fusioncharts using data from MySQL database?
- fusioncharts - Update FusionCharts data without changing chart settings