Menu
I am trying to convert an array obtained by the code below using non-deprecated techniques with php pdo:
$stm = $conn->prepare("SELECT * FROM mysqltable");
$stm->execute();
$results = $stm->fetchAll(PDO::FETCH_ASSOC);
print_r($results);
to the following format required for fusioncharts to be used
[
{
label: "CJ Anderson",
value: "25"
},
{
label: "Imran Tahir",
value: "25"
},
...
...
]
The original array is as follows:
Array (
[0] => Array (
[Id] => 6
[Number] => 1234567890
[Visits] => 1
[Name] => John
)
[1] => Array (
[Id] => 7
[Number] => 1236549871
[Visits] => 9
[Name] => Jerry
)
[2] => Array (
[Id] => 8
[Number] => 2147483647
[Visits] => 3
[Name] => Jane
)
)
Any help would be appreciated, thanks.
EDIT: As I commented below. I have a full php file that works if you put data in manually. I can't get it to work though when I put the $jsonEncodedData in though. Thoughts?
<html>
<head>
<title>FusionCharts XT - Column 2D Chart - Data from a database</title>
<link rel="stylesheet" type="text/css" href="css/style.css" />
<!-- You need to include the following JS file to render the chart.
When you make your own charts, make sure that the path to this JS file is correct.
Else, you will get JavaScript errors. -->
<script src="fusioncharts/js/fusioncharts.js"></script>
</head>
<body>
<?php
try {
# MySQL with PDO_MYSQL
$mysql_host = 'host';
$mysql_database = 'table';
$mysql_username = 'user';
$mysql_password = 'pass';
$conn = new PDO("mysql:host=$mysql_host; dbname=$mysql_database", $mysql_username, $mysql_password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8
}
catch(PDOException $e) {
echo $e->getMessage();
}
// Form the SQL query that returns the top 10 most populous countries
// Execute the query, or else return the error message.
$stm = $conn->prepare("SELECT Name, Visits FROM mysqltable"); //WHERE Area :SelArea");
$stm->execute();
$results = $stm->fetchAll(PDO::FETCH_ASSOC);
include("fusioncharts.php");
$jsnarray = array();
foreach($results as $k => $v){
$jsnarray[] = array('label' => $results[$k]['Name'], 'value' => $results[$k]['Visits']);
};
$jsonEncodedData=json_encode($jsnarray);
new FusionCharts("type of chart",
"unique chart id",
"width of chart",
"height of chart",
"div id to render the chart",
"type of data",
"actual data");
$columnChart = new FusionCharts(
"column2d",
"ex1" ,
"600",
"400",
"chart-1",
"json",
'{
"chart":
{
"caption":"Harry\'s SuperMart",
"subCaption":"Top 5 stores in last month by revenue",
"numberPrefix":"$",
"theme":"ocean"
},
"data": //$jsonEncodedData}'); <---I tried to insert this after "data":but no results unlike if you put raw data**
[
{
"label":"Bakersfield Central",
"value":"880000"
},
{
"label":"Garden Groove harbour",
"value":"730000"
},
{
"label":"Los Angeles Topanga",
"value":"590000"
},
{
"label":"Compton-Rancho Dom",
"value":"520000"
},
{
"label":"Daly City Serramonte",
"value":"330000"
}
]
}');
// Render the chart
$columnChart->render();
?>
<div id="chart-1"><!-- Fusion Charts will render here--></div>
</body>
</html>
==============Edit 12/28/15==========
Tried the following code with no results, Question I have is shouldn't we end in "}" as they require that:
$columnChart = new FusionCharts(
"column2d",
"ex1" ,
"600",
"400",
"chart-1",
"json",
'{
"chart":
{
"caption":"Harry\'s SuperMart",
"subCaption":"Top 5 stores in last month by revenue",
"numberPrefix":"$",
"theme":"ocean"
},
"data": ' . $jsonEncodedData);
//}';
// Render the chart
print_r($columnChart);
$columnChart->render();
?>
<div id="chart-1"><!-- Fusion Charts will render here--></div>
</body>
</html>
I wanted to post the array differences as well between the "manual" method and the "fetch method (above in this edit).
With fetch:
FusionCharts Object ( [constructorOptions:FusionCharts:private] => Array ( >[type] => column2d [id] => ex1 [width] => 600 [height] => 400 [renderAt] => >chart-1 [dataFormat] => json [dataSource] => { "chart": { >"caption":"Harry's SuperMart", "subCaption":"Top 5 stores in last month by >revenue", "numberPrefix":"$", "theme":"ocean" }, "data": >[{"label":"John","value":"125"},{"label":"Jerry","value":"125"},{"label":"Jane","value":"125"}] ) [constructorTemplate:FusionCharts:private] => >[renderTemplate:FusionCharts:private] => )
With Manual Method (that works):
FusionCharts Object ( [constructorOptions:FusionCharts:private] => Array ( >[type] => column2d [id] => ex1 [width] => 600 [height] => 400 [renderAt] => >chart-1 [dataFormat] => json [dataSource] => { "chart": { >"caption":"Harry's SuperMart", "subCaption":"Top 5 stores in last month by >revenue", "numberPrefix":"$", "theme":"ocean" }, "data": [ { >"label":"Bakersfield Central", "value":"880000" }, { "label":"Garden Groove >harbour", "value":"730000" }, { "label":"Los Angeles Topanga", >"value":"590000" }, { "label":"Compton-Rancho Dom", "value":"520000" }, { >"label":"Daly City Serramonte", "value":"330000" } ] } ) >[constructorTemplate:FusionCharts:private] => >[renderTemplate:FusionCharts:private] => )
I see two differences offhand, the manual inserts spaces around "data" and the ending } parameter.
542
There is an automatic (and much much easier) way of doing this:
$stm = $conn->prepare('SELECT Name AS label, Visits AS value FROM mysqltable;');
$stm->execute();
$results = $stm->fetchAll(PDO::FETCH_ASSOC);
$jsonEncodedData = json_encode($results);
echo $jsonEncodedData;
Output (locally tested):
[{"label":"Foo","value":"5"},{"label":"Bar","value":"15"}]
That way you can just use it like this:
$columnChart = new FusionCharts('...
...
"data": ' . $jsonEncodedData . '}');
Note the . '}' in the end.
You could do something like this:
// This part is just for running purposes
$foo = array (
0 => Array (
'Id' => 6,
'Number' => 1234567890,
'Visits' => 1,
'Name' => 'John'
),
1 => array (
'Id' => 7,
'Number' => 1236549871,
'Visits' => 9,
'Name' => 'Jerry'
),
2 => array (
'Id' => 8,
'Number' => 2147483647,
'Visits' => "3", // Example to output quoted
'Name' => 'Jane'
)
);
$bar = array();
foreach($foo as $k => $v){
$bar[] = array('label' => $foo[$k]['Name'], 'value' => $foo[$k]['Visits']);
}
echo json_encode($bar);
Output:
[{"label":"John","value":1},{"label":"Jerry","value":9},{"label":"Jane","value":"3"}]
Compare with yours (from question) in one line:
[{label: "CJ Anderson",value: "25"},{label: "Imran Tahir",value: "25"},...]
Note: I assumed that value is represented by Visit and label by Name.
Read more about json_encode.
111
As a summary this is the piece that solved the issue including FirstOne's foreach statement:
$stm = $conn->prepare("SELECT Name, Visits FROM mysqltable"); //WHERE Area :SelArea");
$stm->execute();
$results = $stm->fetchAll(PDO::FETCH_ASSOC);
include("fusioncharts.php");
$jsnarray = array();
foreach($results as $k => $v){
$jsnarray[] = array('label' => $results[$k]['Name'], 'value' => $results[$k]['Visits']);
};
$jsonEncodedData=json_encode($jsnarray);
//print_r($jsonEncodedData);
new FusionCharts("type of chart",
"unique chart id",
"width of chart",
"height of chart",
"div id to render the chart",
"type of data",
"actual data");
$columnChart = new FusionCharts(
"column2d",
"ex1" ,
"600",
"400",
"chart-1",
"json",
'{
"chart":
{
"caption":"Harry\'s SuperMart",
"subCaption":"Top 5 stores in last month by revenue",
"numberPrefix":"$",
"theme":"ocean"
},
"data": ' . $jsonEncodedData . '}');
// Render the chart
print_r($columnChart);
$columnChart->render();
?>
<div id="chart-1"><!-- Fusion Charts will render here--></div>
</body>
</html>
Thanks for everyone's help in solving the issue.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With