我有一个MySQL查询在PHP中拉回两列的结果
第一列是Label第二列是Value
我读过http://nitschinger.at/Handling-JSON-like-a-boss-in-PHP,但我很难理解我如何能做以下JSON PHP从MySQL
[
{
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
}
]
}
]
我可以像上面那样手动编写一个循环来输出原始文本以形成JSON,但我真的想使用json_encode
<?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();
}
?>
这能做到吗?如果有,那是怎么回事?
使用json_encode()
$jsonArr = array( 'key' => 'Cumulative Return' , 'values' => array() );
foreach ($result as $res)
{
$jsonArr['values'][] = array('label'=>$res['Label'],'value'=>$res['value']);
}
echo json_encode($jsonArr);
试试这个:
$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();
}
你只需要按照你想要转换为json的方式创建数组:
$encode = array(
'key' => 'Cumulative Return',
'values' => $result->fetchAll(PDO::FETCH_ASSOC)
);
echo json_encode($encode);
这就是JSON的有用之处-您不需要手动编码它。
$json = array();
$results = array();
$json['key'] = 'Cumulative return';
foreach ($row as $entry) {
$results['label'] = $entry['Label'];
$results['value'] = $entry['Value'];
$json['value'][] = $results;
}
echo json_encode($json);
就是这样。享受吧!
我使用代码点火器
这是我如何从数据库的值创建自定义json希望它有助于
function getData() {
$query = $this -> db -> get('gcm_users');
$result_array=array();
if ($query -> num_rows() > 0) {
$results=$query->result();
foreach ( $results as $row) {
$data=array();
$data['name']=$row->name;
$data['email']=$row->email;
array_push($result_array,$data) ;
}
return json_encode($result_array);
} else {
echo "something went wrong";
}
}
这是我从数据库中检索数据的功能,因为我使用框架工作,如代码点燃器,我不必担心我的其他sql操作,希望你可以从这个代码中提取你需要的东西。
输出如下所示
[{"name":"allu","email":"allu@gmail.com"},{"name":"ameeen","email":"cdfdfd@gmail.com"}]
我构建了一个简单的函数(我更喜欢使用它而不是json_encode -因为我可以完全控制所获取的数据):
public function mysqlToJoson($mysqlarray)
{
$json_string = "[";
while ($array = $mysqlarray->fetch()) {
$i = 1;
if ($json_string != "["){$json_string .= ",";}
foreach ($array as $key => $value) {
if ($i == 1) {
$json_string .= '{"' . $key . '":' . '"' . htmlentities($value,ENT_QUOTES, 'UTF-8') . '"';
} else $json_string .= ',"' . $key . '":' . '"' . htmlentities($value,ENT_QUOTES, 'UTF-8') . '"';
$i++;
}
$json_string .= '}';
}
$json_string .= ']';
return $json_string;
}