RTime

Error data output (json) stackedcolumn2d

By RTime, in FusionCharts and PHP

Recommended Posts

RTime   

RTime
Posted

Hi everyone, i can't get a stackedcolumn2d chart to work, the query is correct, instead the json output returns wrong data, how is this possible?

This is output MySql query

answer   station answer_count
NULL APR 61
NULL AQ1 61
NULL AR 61
NULL AT 61
NULL BA 61
NULL BDG 61
NULL BG 61
NULL BIE 61
NULL BNS 61
NULL BO1 61
NULL BO14 61
NULL BS 61
NULL BUS 61
NULL BZ 61
YES AG 61
YES AL 37
YES AN 48
YES AO 58
NO AL 13
NO AN 10
NO AO 2
N/A AL 11
N/A AN 3
N/A AO 1

 

And this is output json, is not correct, have you any ideas?

categories":[{"
category":[{"label":"AG"},{"label":"AL"},{"label":"AN"},{"label":"AO"},{"label":"APR"},{"label":"AQ1"},{"label":"AR"},{"label":"AT"},{"label":"BA"},{"label":"BDG"},{"label":"BG"},{"label":"BIE"},{"label":"BNS"},{"label":"BO1"},{"label":"BO14"},{"label":"BS"},{"label":"BUS"},{"label":"BZ"}]}],"
dataset":[{
"seriesname":"NULL","data":[{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"}]},{
"seriesname":"YES","data":[{"value":"61"},{"value":"61"},{"value":"37"},{"value":"48"},{"value":"58"}]},{
"seriesname":"NO","data":[{"value":"13"},{"value":"13"},{"value":"10"},{"value":"2"}]},{
"seriesname":"N\/A","data":[{"value":"11"},{"value":"11"},{"value":"3"},{"value":"1"}]}]}

Share this post

Link to post
Share on other sites

Ayan Bhadury   

Ayan Bhadury
Posted
On 1/13/2021 at 5:04 AM, RTime said:

Hi everyone, i can't get a stackedcolumn2d chart to work, the query is correct, instead the json output returns wrong data, how is this possible?

This is output MySql query

answer   station answer_count
NULL APR 61
NULL AQ1 61
NULL AR 61
NULL AT 61
NULL BA 61
NULL BDG 61
NULL BG 61
NULL BIE 61
NULL BNS 61
NULL BO1 61
NULL BO14 61
NULL BS 61
NULL BUS 61
NULL BZ 61
YES AG 61
YES AL 37
YES AN 48
YES AO 58
NO AL 13
NO AN 10
NO AO 2
N/A AL 11
N/A AN 3
N/A AO 1

 

And this is output json, is not correct, have you any ideas?

categories":[{"
category":[{"label":"AG"},{"label":"AL"},{"label":"AN"},{"label":"AO"},{"label":"APR"},{"label":"AQ1"},{"label":"AR"},{"label":"AT"},{"label":"BA"},{"label":"BDG"},{"label":"BG"},{"label":"BIE"},{"label":"BNS"},{"label":"BO1"},{"label":"BO14"},{"label":"BS"},{"label":"BUS"},{"label":"BZ"}]}],"
dataset":[{
"seriesname":"NULL","data":[{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"},{"value":"61"}]},{
"seriesname":"YES","data":[{"value":"61"},{"value":"61"},{"value":"37"},{"value":"48"},{"value":"58"}]},{
"seriesname":"NO","data":[{"value":"13"},{"value":"13"},{"value":"10"},{"value":"2"}]},{
"seriesname":"N\/A","data":[{"value":"11"},{"value":"11"},{"value":"3"},{"value":"1"}]}]}

Here is a demo based on your data  : http://jsfiddle.net/x937haw5/1/

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted

Many thanks Ayan Bhadury,
I solved part of the problem with a new query. I understand that the staked chart needs a name-value pair, now the query output is correct.

answer   station answer_count
N/A AG 0
N/A AL 11
N/A AN 3
N/A AO 1
N/A APR 0
N/A AQ1 0
N/A AR 0
N/A AT 0
N/A BA 0
NO AG 0
NO AL 13
NO AN 10
NO AO 2
NO APR 0
NO AQ1 0
NO AR 0
NO AT 0
NO BA 0
NULL AG 0
NULL AL 0
NULL AN 0
NULL AO 0
NULL APR 61
NULL AQ1 61
NULL AR 61
NULL AT 61
NULL BA 61
YES AG 61
YES AL 37
YES AN 48
YES AO 58
YES APR 0
YES AQ1 0
YES AR 0
YES AT 0
YES BA 0

But as you can see there is still an error in the json output. The query data is not correctly displayed in the chart.

I used this php script to generate the json 

<?php
include("DBConn.php");
include("fusioncharts/fusioncharts.php");

$strQueryCategories = "SELECT DISTINCT station FROM v_answer";
$resultCategories = $conn->query($strQueryCategories)or exit("Error code ({$conn->errno}): {$conn->error}");

$strQueryData = "SELECT answer, station, answer_count FROM v_answer";
$resultData = $conn->query($strQueryData)or exit("Error code ({$conn->errno}): {$conn->error}");

if ($resultData) {

    $arrData = array(
        "chart" => array(
            "caption" => "",
            "bgColor" => "#ffffff",
            "borderAlpha" => "",
            "showBorder"=> "0",
            "showCanvasBg" => "0",
            "showCanvasBase" => "1",
            "canvasBaseDepth" => "14",
            "canvasBgDepth" => "5",
            "canvasBaseColor" => "#ffffff",
            "canvasBgColor" => "#ffffff",
            "theme"=> "fusion",
            "palettecolors" => "#e0e0e0,#13ac0b,#f72b10,#f59054",
     
        )
    );

    $arrData["categories"] = array(array("category" => array()));
    if ($resultCategories) {
    	$controlBreakValue = "";
        while ($row = mysqli_fetch_array($resultCategories)) {
            if ($controlBreakValue != $row["station"]) {
                $controlBreakValue = $row["station"];
	            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["answer"]) {
                $controlBreakValue = $row["answer"];
                array_push($arrData["dataset"], array("seriesname" => $controlBreakValue, "data" => array(array("value" => $row["answer_count"]))));
                $controlBreakValue == "";
                $i++;
            }
            array_push($arrData["dataset"][$i -1]["data"], array("value" => $row["answer_count"]));
        }
    }
}

$jsonEncodedData = json_encode($arrData);

$conn->close();

echo $jsonEncodedData;
?>


 

Share this post

Link to post
Share on other sites

Ayan Bhadury   

Ayan Bhadury
Posted

What is the error you are getting? could you please share the JSON data that is retrieved after the chart is rendered? You can check the view page source and share the JSON data or you in the browser console type FusionCharts.items[''chart-object"].getJSONData()

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted 
{"chart":{"caption":"","bgColor":"#ffffff","borderAlpha":"","showBorder":"0","showCanvasBg":"0","showCanvasBase":"1","canvasBaseDepth":"14","canvasBgDepth":"5","canvasBaseColor":"#ffffff","canvasBgColor":"#ffffff","theme":"fusion","palettecolors":"#e0e0e0,#13ac0b,#f72b10,#f59054"},"categories":[{"category":[{"label":"AG"},{"label":"AL"},{"label":"AN"},{"label":"AO"},{"label":"APR"},{"label":"AQ1"},{"label":"AR"},{"label":"AT"},{"label":"BA"},{"label":"BDG"},{"label":"BG"},{"label":"BIE"},{"label":"BNS"},{"label":"BO1"},{"label":"BO14"},{"label":"BS"},{"label":"BUS"},{"label":"BZ"}]}],"dataset":[{"seriesname":"NULL","data":[{"value":"0"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=1"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=2"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=3"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=5"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=6"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=7"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=8"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=9"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=10"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=12"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=13"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=14"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=15"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=16"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=17"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=18"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=19"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=20"}]},{"seriesname":"SI","data":[{"value":"61"},{"value":"61","link":"sa_chart_station_detail.php?pid_station=1"},{"value":"37","link":"sa_chart_station_detail.php?pid_station=2"},{"value":"48","link":"sa_chart_station_detail.php?pid_station=3"},{"value":"58","link":"sa_chart_station_detail.php?pid_station=5"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=6"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=7"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=8"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=9"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=10"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=12"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=13"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=14"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=15"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=16"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=17"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=18"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=19"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=20"}]},{"seriesname":"NO","data":[{"value":"0"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=1"},{"value":"13","link":"sa_chart_station_detail.php?pid_station=2"},{"value":"10","link":"sa_chart_station_detail.php?pid_station=3"},{"value":"2","link":"sa_chart_station_detail.php?pid_station=5"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=6"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=7"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=8"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=9"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=10"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=12"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=13"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=14"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=15"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=16"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=17"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=18"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=19"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=20"}]},{"seriesname":"N\/A","data":[{"value":"0"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=1"},{"value":"11","link":"sa_chart_station_detail.php?pid_station=2"},{"value":"3","link":"sa_chart_station_detail.php?pid_station=3"},{"value":"1","link":"sa_chart_station_detail.php?pid_station=5"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=6"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=7"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=8"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=9"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=10"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=12"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=13"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=14"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=15"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=16"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=17"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=18"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=19"},{"value":"0","link":"sa_chart_station_detail.php?pid_station=20"}]}]}

json outuput

and this is

query output
 

pid_station num_risposta tipo_risposta station totali  
1 0 NULL AG 0
2 0 NULL AL 0
3 0 NULL AN 0
5 0 NULL AO 0
6 0 NULL APR 61
7 0 NULL AQ1 61
8 0 NULL AR 61
9 0 NULL AT 61
10 0 NULL BA 61
12 0 NULL BDG 61
13 0 NULL BG 61
14 0 NULL BIE 61
15 0 NULL BNS 61
16 0 NULL BO1 61
17 0 NULL BO14 61
18 0 NULL BS 61
19 0 NULL BUS 61
20 0 NULL BZ 61
1 1 SI AG 61
2 1 SI AL 37
3 1 SI AN 48
5 1 SI AO 58
6 1 SI APR 0
7 1 SI AQ1 0
8 1 SI AR 0
9 1 SI AT 0
10 1 SI BA 0
12 1 SI BDG 0
13 1 SI BG 0
14 1 SI BIE 0
15 1 SI BNS 0
16 1 SI BO1 0
17 1 SI BO14 0
18 1 SI BS 0
19 1 SI BUS 0
20 1 SI BZ 0
1 2 NO AG 0
2 2 NO AL 13
3 2 NO AN 10
5 2 NO AO 2
6 2 NO APR 0
7 2 NO AQ1 0
8 2 NO AR 0
9 2 NO AT 0
10 2 NO BA 0
12 2 NO BDG 0
13 2 NO BG 0
14 2 NO BIE 0
15 2 NO BNS 0
16 2 NO BO1 0
17 2 NO BO14 0
18 2 NO BS 0
19 2 NO BUS 0
20 2 NO BZ 0
1 3 N/A AG 0
2 3 N/A AL 11
3 3 N/A AN 3
5 3 N/A AO 1
6 3 N/A APR 0
7 3 N/A AQ1 0
8 3 N/A AR 0
9 3 N/A AT 0
10 3 N/A BA 0
12 3 N/A BDG 0
13 3 N/A BG 0
14 3 N/A BIE 0
15 3 N/A BNS 0
16 3 N/A BO1 0
17 3 N/A BO14 0
18 3 N/A BS 0
19 3 N/A BUS 0
20 3 N/A BZ 0

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted

Firstly, as you can see the first value of the dataset is a zero that shouldn't come up, this in my opinion is wrong. 

{"value":"0"},

 

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted
Quote

or you in the browser console type FusionCharts.items[''chart-object"].getJSONData()

Excuse me ... how can I do a check in the firefox or chrome console?

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted

Sorry, can you try to render a scrollstackedcolumn2d chart with the data I shared with you?
You will see that the data that the chart presents is not correct

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted

What I mean is that the dataset should start with;
  

"dataset": [{
            "seriesname": "NULL",
            "data": [{
              "value": null,
              "link": "sa_chart_station_detail.php?pid_station=1"
            },

instead it starts with;
  

"dataset": [{
            "seriesname": "NULL",
            "data": [{
              "value": null
            }, {
              "value": null,
              "link": "sa_chart_station_detail.php?pid_station=1"
            },

 

Share this post

Link to post
Share on other sites

Ayan Bhadury   

Ayan Bhadury
Posted
12 minutes ago, RTime said:

What I mean is that the dataset should start with;
  


"dataset": [{
            "seriesname": "NULL",
            "data": [{
              "value": null,
              "link": "sa_chart_station_detail.php?pid_station=1"
            },

instead it starts with;
  


"dataset": [{
            "seriesname": "NULL",
            "data": [{
              "value": null
            }, {
              "value": null,
              "link": "sa_chart_station_detail.php?pid_station=1"
            },

 

Please update the demo with your chart data ; https://jsfiddle.net/qjd56g8h/

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted

the data that the graph presents continues to be wrong.
The first column should have the data of the second and so on ...

the first column (AG) must have the following values;
YES = 61

the second column (AN) must have;
YES = 37
NO = 13
N / A = 11

the third column must have;
YES = 48
NO = 10
N / A = 3

and so on, in practice the first values of the output must not be there

https://jsfiddle.net/z4etx58f/

Share this post

Link to post
Share on other sites

Ayan Bhadury   

Ayan Bhadury
Posted
On 1/15/2021 at 8:29 AM, RTime said:

the data that the graph presents continues to be wrong.
The first column should have the data of the second and so on ...

the first column (AG) must have the following values;
YES = 61

the second column (AN) must have;
YES = 37
NO = 13
N / A = 11

the third column must have;
YES = 48
NO = 10
N / A = 3

and so on, in practice the first values of the output must not be there

https://jsfiddle.net/z4etx58f/

The example which you have shared does not have any series as YES, please note FusionCharts does not manipulate the data, please structure the data properly and visualize the data based on that.

Share this post

Link to post
Share on other sites

Ayan Bhadury   

Ayan Bhadury
Posted
On 1/16/2021 at 4:25 AM, RTime said:

To generate the correct graph I had to manually remove from the js (jsfiddle) box the first values that duplicated in the json.
The problem is most likely in the PHP code that generates the json but I just can't figure out what it is.
https://jsfiddle.net/4ef2oz53/

Please check your implementation logic

Share this post

Link to post
Share on other sites

Ayan Bhadury   

Ayan Bhadury
Posted
On 1/15/2021 at 10:41 AM, RTime said:

Excuse me Ayan,
Is it possible to assign palletscolor by variable name in multiseries charts?

yes you can store the values in array or in object or in a variable and fetch them as per your choice

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted
46 minutes ago, Ayan Bhadury said:

yes you can store the values in array or in object or in a variable and fetch them as per your choice

can you give me an example please?

In the logic I use (and which I downloaded from a zip file posted by a colleague of yours) how should I set to always display the same colors for certain values (YES = green, NO = red, N / A = orange and so on)?

Share this post

Link to post
Share on other sites

Ayan Bhadury   

Ayan Bhadury
Posted
4 hours ago, RTime said:

can you give me an example please?

In the logic I use (and which I downloaded from a zip file posted by a colleague of yours) how should I set to always display the same colors for certain values (YES = green, NO = red, N / A = orange and so on)?

Please help me with your licensing details.

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted
2 hours ago, Ayan Bhadury said:

Please help me with your licensing details.

I don't have the license yet, I'm using the trial version, I'm still considering whether to buy it

Share this post

Link to post
Share on other sites

Ayan Bhadury   

Ayan Bhadury
Posted
8 minutes ago, RTime said:

I don't have the license yet, I'm using the trial version, I'm still considering whether to buy it

okay, for php implementation please check other web forums or kindly get our support license, if you face any problem with the chart library I would be assisting you accordingly, 

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted

Resolved!
I have corrected the code for generating the json.
I share the solution for those who need it. 

$arrData["dataset"] = array();
$i = 0;
if ($resultData) {
	$controlBreakValue = "";
	while ($row = mysqli_fetch_array($resultData)) {
		if ($controlBreakValue != $row["data_name"]) {
			$controlBreakValue = $row["data_name"];
			array_push($arrData["dataset"], array("seriesname" => $controlBreakValue, "data" => array(array("value" => $row["totals"],"link" => "detail.php?id=".$row["id"]))));
			$controlBreakValue == "";
			$i++;
		}else{
			array_push($arrData["dataset"][$i - 1]["data"], array("value" => $row["totals"],"link" => "detail.php?id=".$row["id"]));
		}
	}
}

 

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted (edited)
On 18/1/2021 at 7:36 AM, Ayan Bhadury said:

yes you can store the values in array or in object or in a variable and fetch them as per your choice

Excuse me,
I tried to set the color for each single value directly in the query and then add it together with the label and value but it doesn't work.
Exactly what is the term for coloring columns?

I used this code 

$arrData["data"] = array();
while ($row = mysqli_fetch_array($result)) {
	array_push($arrData["data"], array(
		"label"         => $row["type_answers"],
		"value"         => $row["perc_answers"],
		"palettecolors" => $row["palettecolor"]
	   )
	);
}

Thanks in advance

Edited by RTime

Share this post

Link to post
Share on other sites

RTime   

RTime
Posted

Resolved!
I had to write color!!! ;)

$arrData["data"] = array();
while ($row = mysqli_fetch_array($result)) {
	array_push($arrData["data"], array(
		"label"         => $row["type_answers"],
		"value"         => $row["perc_answers"],
		"color" 	=> $row["palettecolor"]
	   )
	);
}

 

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
Go To Topic Listing FusionCharts and PHP