I have a MySQL query in PHP that pulls back two columns of results
Column One is Label Column Two is Value
I have read http://nitschinger.at/Handling-JSON-like-a-boss-in-PHP but im struggling to understand how I can do the following JSON in PHP from MySQL
JavaScript
x
[
{
key: "Cumulative Return",
values: [
{
"label": "One",
"value" : 29.765957771107
} ,
{
"label": "Two",
"value" : 0
} ,
{
"label": "Three",
"value" : 32.807804682612
} ,
{
"label": "Four",
"value" : 196.45946739256
} ,
{
"label": "Five",
"value" : 0.19434030906893
} ,
{
"label": "Six",
"value" : 98.079782601442
} ,
{
"label": "Seven",
"value" : 13.925743130903
} ,
{
"label": "Eight",
"value" : 5.1387322875705
}
]
}
]
I can manually write a loop to output the raw text to form a JSON like above but I really want to use json_encode
JavaScript
<?php
$hostname = 'localhost'; //MySQL database
$username = 'root'; //MySQL user
$password = ''; //MySQL Password
try {
$dbh = new PDO("mysql:host=$hostname;dbname=etl", $username, $password);
$query = "select label, value from table";
$stmt = $dbh->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!empty($result)) {
echo '[
{
key: "Cumulative Return",
values: [';
for ($row = 0; $row < count($result); $row++) {
echo "{";
echo '"label": "' . $result[$row]['Label'] . '",';
echo '"Value": ' . $result[$row]['Value'];
echo "},";
echo ' ]
}
]';
}
}
}
catch (PDOException $e) {
echo $e->getMessage();
}
?>
Can this be done? if so how?
Advertisement
Answer
Try this:
JavaScript
$hostname = 'localhost'; //MySQL database
$username = 'root'; //MySQL user
$password = ''; //MySQL Password
try {
$dbh = new PDO("mysql:host=$hostname;dbname=etl", $username, $password);
$query = "select label, value from table";
$stmt = $dbh->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$values = array();
for($row = 0; $row < count($result); $row++)
{
$values[] = array('label' => $result[$row]['Label'], 'value' => $result[$row]['Value']);
}
$to_encode = array(
array('key' => 'Cumulative Return',
'values' => $values;
)
);
echo json_encode($to_encode);
} catch (PDOException $e) {
echo $e->getMessage();
}