php 将数据从 MySQL 拉入 json 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10184655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pulling data from MySQL into json array
提问by Kyle Monti
I'm trying to pull data from my database using json in php. I have a few elements I need to specific then to post them on a page.
我正在尝试使用 php 中的 json 从我的数据库中提取数据。我有一些需要具体说明的元素，然后才能将它们发布到页面上。
I want to "fetch" the data from mysql and return it to a json_encode. How can I do this using the SELECT method. Some had used PDO methods and other have used mysql_assoc, which confuses me.
我想从mysql“获取”数据并将其返回到json_encode。如何使用 SELECT 方法执行此操作。有些人使用了 PDO 方法，其他人使用了 mysql_assoc，这让我很困惑。
For instance,
例如，
I have rows of: 'id' , 'title' , 'start', 'backgroundColor'...etc. along with a default value for all of them. ($array[] = "someValue = default")
我有几行： 'id' 、 'title' 、 'start'、 'backgroundColor'...等。以及所有这些的默认值。($array[] = "someValue = default")
I want it to export like so:
我希望它像这样导出：
array(
'id' => 1,
'title' => "someTitle",
'start' => "2012-04-16",
'backgroundColor' => "blue",
'someValue' = > "default",
...
), ....
));
If anyone could help me with this with the best detail, I'd be awesome!
如果有人能以最好的细节帮助我解决这个问题，我会很棒！
回答by Lawrence Cherone
If you wanted to do this with PDO then here is an example:
如果你想用 PDO 做到这一点，那么这里是一个例子：
<?php
$dbh = new PDO("mysql:host=localhost;dbname=DBNAME", $username, $password);
$sql = "SELECT `id`, `title`, `time`, `start`, `backgroundColor`
FROM my_table";
$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
//To output as-is json data result
//header('Content-type: application/json');
//echo json_encode($result);
//Or if you need to edit/manipulate the result before output
$return = [];
foreach ($result as $row) {
$return[] = [
'id' => $row['id'],
'title' => $row['title'],
'start' => $row['start'].' '.$row['time'],
'backgroundColor' => $row['backgroundColor']
];
}
$dbh = null;
header('Content-type: application/json');
echo json_encode($return);
?>
回答by Marc B
You don't "fetch to a json array".
您不会“获取到 json 数组”。
You fetch your database results into a PHP array, then convert that php array, AFTER THE FETCHING IS COMPLETED, to a json string.
您将数据库结果提取到一个 PHP 数组中，然后在提取完成后将该 php 数组转换为 json 字符串。
e.g.
例如
$data = array();
while ($row = mysql_fetch_assoc($results)) {
$data[] = $row;
}
echo json_encode($data);
回答by tinybai
You can get the result from mysql,then format it to json
你可以从mysql获取结果，然后将其格式化为json
$array = array();
while($row = mysqli_fetch_array($result))
{
array_push($array,$row);
}
$json_array = json_encode($array);
回答by Dhiraj
Please check for SELECT methods here
请在此处检查 SELECT 方法
In general it would look like this
一般来说，它看起来像这样
$data = array(); // result variable
$i=0
$query = "SELECT id,title,start,backgroundColor FROM my_table"; // query with SELECT
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){ // iterate over results
$data['item'][$i]['id'] = $row['id']; // rest similarly
...
...
$i++;
}
header('Content-type: application/json'); // display result JSON format
echo json_encode(array(
'success' => true,
'data' => $data // this is your data variable
));
