使用 PHP 从 MYSQL 获取数据到 JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4816270/
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
Getting data from MYSQL into JSON using PHP
提问by Lee Armstrong
I have the following quite simple test PHP code that extracts the data and puts it into JSON formatted text.
我有以下非常简单的测试 PHP 代码，用于提取数据并将其放入 JSON 格式的文本中。
I get the following error..
我收到以下错误..
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 1979603 bytes) in /var/www/test.php on line 33
致命错误：第 33 行的 /var/www/test.php 中允许的内存大小为 33554432 字节（试图分配 1979603 字节）
Where line 33 is the json_encode()line.
其中第 33 行是该json_encode()行。
Is there a way to make this more efficient? The PHP.iniis already set to 32M as max, hence sized up from the 8M standard!
有没有办法使这更有效？在PHP.ini已设置为32M为最大，因此从8M标准尺寸的了！
<?php
require('../../admin/db_login.php');
$db=mysql_connect($host, $username, $password) or die('Could not connect');
mysql_select_db($db_name, $db) or die('');
$result = mysql_query("SELECT * from listinfo") or die('Could not query');
$json = array();
if(mysql_num_rows($result)){
$row=mysql_fetch_assoc($result);
while($row=mysql_fetch_row($result)){
// cast results to specific data types
$test_data[]=$row;
}
$json['testData']=$test_data;
}
mysql_close($db);
echo json_encode($json);
?>
回答by Andrew Moore
You are probably encoding a very large dataset. You could encode each row, one row at a time instead of encoding it in one big operation.
您可能正在编码一个非常大的数据集。您可以对每一行进行编码，一次一行，而不是在一次大操作中对其进行编码。
<?php
require('../../admin/db_login.php');
$db=mysql_connect($host, $username, $password) or die('Could not connect');
mysql_select_db($db_name, $db) or die('');
$result = mysql_query("SELECT * from listinfo") or die('Could not query');
if(mysql_num_rows($result)){
echo '{"testData":[';
$first = true;
$row=mysql_fetch_assoc($result);
while($row=mysql_fetch_row($result)){
// cast results to specific data types
if($first) {
$first = false;
} else {
echo ',';
}
echo json_encode($row);
}
echo ']}';
} else {
echo '[]';
}
mysql_close($db);
That way, each call to json_encode()only encodes a small array instead of a large one. The end result is the same. This is IMO the solution which will use the less memory.
这样，每次调用json_encode()只编码一个小数组而不是一个大数组。最终结果是一样的。这是 IMO 将使用较少内存的解决方案。
回答by Mark Baker
Stop duplicating your array of data
停止复制您的数据数组
$json = array();
if(mysql_num_rows($result)){
$row=mysql_fetch_assoc($result);
while($row=mysql_fetch_row($result)){
// cast results to specific data types
$json['testData'][]=$row;
}
}
that will help reduce your memory usage
这将有助于减少您的内存使用量
回答by Rohit Suthar
Use this one:
使用这个：
$result = mysql_query("SELECT * FROM listinfo");
$json = array();
$total_records = mysql_num_rows($result);
if($total_records > 0){
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$json[] = $row;
}
}
echo json_encode($json);
回答by Zsub
As a first work-around set it to something like 256M or even 512M.
作为第一个解决方法，将其设置为 256M 甚至 512M。
It is probable the dataset that MySQL is returning to you is quite big. So even if your PHP is very memory-efficient, you still will get the OoM error. So as a more viable long-term solution use the LIMITstatement (SELECT * FROM $table WHERE 1=1 LIMIT 0,30(start from index 0, get 30 items).
MySQL 返回给您的数据集可能相当大。因此，即使您的 PHP 非常节省内存，您仍然会遇到 OoM 错误。因此，作为更可行的长期解决方案，请使用LIMIT语句 ( SELECT * FROM $table WHERE 1=1 LIMIT 0,30(从索引 0 开始，获取 30 个项目)。
EDIT: Oh wow, I didn't even see the problem from the first solution... Well, still might be a good idea to LIMITyour query :-)
编辑：哦，哇，我什至没有从第一个解决方案中看到问题......好吧，对于LIMIT您的查询仍然可能是一个好主意:-)
回答by Ananth
Here is my first json that works perfectly
这是我的第一个完美运行的 json
<?php
// connect to mysql server
mysql_connect($host, $username, $password) or die('Could not connect');
// select the db name
mysql_select_db($dbname);
// enter your sql query
$sql = "Select * from Order_Details";
// Creates temp array variable
$temp = array();
// Gets table details
$result = mysql_query($sql);
// Adds each records/row to $temp
while($row=mysql_fetch_row($result)) {
$temp[] = $row;
}
// Formats json from temp and shows/print on page
echo json_encode($temp);
?>
