Ventr

Multi Series 2d column chart from MySQL

Recommended Posts

Hello.

I'm trying to create a 2D chart with MySQL data that would show two data ranges. For previous and current results. I have to use two queries

1. "SELECT name, points FROM results GROUP BY name";

2. "SELECT name, pointsold FROM results GROUP BY name";

How to combine this in code?

<?php

     	// Form the SQL query that returns the top 10 most populous countries
     	$strQuery = "SELECT name, points FROM results GROUP BY name";

     	// Execute the query, or else return the error message.
     	$result = $dbhandle->query($strQuery) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");

     	// 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" => "Rozkład punktacji",
              "paletteColors" => "#0075c2",
              "bgColor" => "#ffffff",
              "borderAlpha"=> "20",
              "canvasBorderAlpha"=> "0",
              "usePlotGradientColor"=> "0",
              "plotBorderAlpha"=> "0",
              "showXAxisLine"=> "1",
              "xAxisLineColor" => "#999999",
              "showValues" => "1",
              "divlineColor" => "#999999",
              "divLineIsDashed" => "1",
              "showAlternateHGridColor" => "0"
            )
           	);

        	$arrData["data"] = array();

	// Push the data into the array
        	while($row = mysqli_fetch_array($result)) {
           	array_push($arrData["data"], array(
              	"label" => $row["nazwa"],
              	"value" => $row["punkty"]
              	)
           	);
        	}

        	/*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 column 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.*/

        	$columnChart = new FusionCharts("column2D", "myFirstChart" , 600, 300, "chart-2", "json", $jsonEncodedData);

        	// Render the chart
        	$columnChart->render();

        	// Close the database connection
        	$dbhandle->close();
     	}

  	?>          

 

Edited by Ventr

Share this post


Link to post
Share on other sites

Hello,

Thanks for the query.

The code which you have shared is for single series chart, for multi-series chart the json format is different.

Please modify the php code to generate the json structure required to render multi-series chart using FusionCharts.

Kindly find the sample attached below for the implementation.

Please note : To make this sample work, kindly modify the database connection as per your set-up and import the database table provided with the sample.

mscolumn2d_mysql.zip

Share this post


Link to post
Share on other sites
On 5/8/2017 at 11:05 AM, Prerana said:

Hello,

Thanks for the query.

The code which you have shared is for single series chart, for multi-series chart the json format is different.

Please modify the php code to generate the json structure required to render multi-series chart using FusionCharts.

Kindly find the sample attached below for the implementation.

Please note : To make this sample work, kindly modify the database connection as per your set-up and import the database table provided with the sample.

mscolumn2d_mysql.zip

Hello,

We are trying to implement this, and this appears to only support 1 label and multiple values, however we are trying to plot the label against a date too, our table structure is:

Date | Column 1 | Column 2

01/01/2016 | 300 | 1616

01/01/2016 | 100 | 1000

02/01/2016 | 193 | 1616

02/01/2016 | 492 | 1000

Column 2 contains the names of the series we wish to plot, column 1 is the value and the 'Date' column is what date we wish to plot too.

 

 

 

Share this post


Link to post
Share on other sites
12 minutes ago, Prerana said:

Hello,

Have you referred the sample I have provided?

Using that sample, your requirement is achievable. Kindly check once.

Hi Prerana, I have however I can't get it to display correctly, as I have duplicate dates and blanks in my table.

 

postgr.PNG

Share this post


Link to post
Share on other sites

Hello,

You can easily make it display correctly by defining primary key for each row.

Please try using this solution using the source code and the SQL dump we have shared in the earlier response, and let us know.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now