php 如何在php mysql中获取结果的所有行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10940332/
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
how to fetch all the row of the result in php mysql?
提问by mprog
In my table I have 2 records with companyid = 1, but when I run the php below for companyid = 1it returns only the first one !
How can I fetch all the records?
在我的表中，我有 2 条记录companyid = 1，但是当我运行下面的 php 时，companyid = 1它只返回第一个！
如何获取所有记录？
The php file:
php文件：
if (isset($_GET["companyid"])) {
$companyid = $_GET['companyid'];
// get a product from products table
$result = mysql_query("SELECT * FROM `products`
WHERE companyid = $companyid;");
if (!empty($result)) {
if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)){
$product = array();
$product["pid"] = $row["pid"];
$product["productname"] = $row["productname"];
}
$response["product"] = array();
array_push($response["product"], $product);
// success
$response["success"] = 1;
echo json_encode($response);
} else {
// no product found
$response["success"] = 0;
$response["message"] = "No product found";
// echo no product JSON
echo json_encode($response);
}
} else {
// no product found
$response["success"] = 0;
$response["message"] = "No product found";
// echo no users JSON
echo json_encode($response);
}
} else {
$response["success"] = 0;
$response["message"] = "Required field(s) is missing";
// echoing JSON response
echo json_encode($response);
}
Using mysql_fetch_arrayis happening the same.
it returns {"product":[{"pid":"12371","productname":"test"}],"success":1}when i run a query without parameters select * from tableusing mysql_fetch_arrayit returns all the rows ..
使用mysql_fetch_array正在发生同样的事情。它返回{"product":[{"pid":"12371","productname":"test"}],"success":1}当我运行不带参数的查询select * from table使用mysql_fetch_array它返回所有的行..
回答by Andy Braham
As NikiC pointed out you should not be using the mysql_ functions any longer, you can fetch the entire array in both PDO and mysqli, Here is a example to fetch all rows using the mysqli->fetch_allfunction, hope this helps!
正如 NikiC 指出你不应该再使用 mysql_ 函数，你可以在 PDO 和 mysqli 中获取整个数组，这是一个使用mysqli->fetch_all函数获取所有行的示例，希望这有帮助！
//Database Connection
$sqlConn = new mysqli($hostname, $username, $password, $database);
//Build SQL String
$sqlString = "SELECT * FROM my_table";
//Execute the query and put data into a result
$result = $sqlConn->query($sqlString);
//Copy result into a associative array
$resultArray = $result->fetch_all(MYSQLI_ASSOC);
//Copy result into a numeric array
$resultArray = $result->fetch_all(MYSQLI_NUM);
//Copy result into both a associative and numeric array
$resultArray = $result->fetch_all(MYSQLI_BOTH);
回答by Fernando André
while ($row = mysql_fetch_assoc($result)) {
echo $row["userid"];
echo $row["fullname"];
echo $row["userstatus"];
}
mysql_free_result($result);
I would recommend you to use PDO instead of mysql_
我建议你使用 PDO 而不是 mysql_
if (!empty($result)) {
Could be is_resource($result)
可能 is_resource($result)
回答by Tim Withers
You need to loop through the result to pull all the rows:
您需要遍历结果以拉出所有行：
while($row = mysql_fetch_assoc($result)){
//Do stuff
}
On a side note, you should be using at least mysqli or PDO instead of mysql_* functions.
附带说明一下，您应该至少使用 mysqli 或 PDO 而不是 mysql_* 函数。
回答by Lukas Lukac
I strongly believe the batch processing with Doctrine or any kind of iterations with MySQL (PDO or mysqli) are just an illusion.
我坚信使用 Doctrine 进行批处理或使用 MySQL（PDO 或 mysqli）进行任何类型的迭代只是一种幻觉。
@dimitri-k provided a nice explanation especially about unit of work. The problem is the miss leading: "$query->iterate()" which doesn't really iterate over the data source. It's just an \Traversable wrapperaround already fully fetcheddata source.
@dimitri-k 提供了一个很好的解释，尤其是关于工作单元的解释。问题是未命中领先：“ $query->iterate()”，它并没有真正遍历数据源。它只是一个围绕已经完全获取的数据源的 \Traversable 包装器。
An example demonstrating that even removing Doctrine abstraction layer completely from the picture, we will still run into memory issues:
一个例子证明即使从图片中完全删除 Doctrine 抽象层，我们仍然会遇到内存问题：
echo 'Starting with memory usage: ' . memory_get_usage(true) / 1024 / 1024 . " MB \n";
$pdo = new \PDO("mysql:dbname=DBNAME;host=HOST", "USER", "PW");
$stmt = $pdo->prepare('SELECT * FROM my_big_table LIMIT 100000');
$stmt->execute();
while ($rawCampaign = $stmt->fetch()) {
// echo $rawCampaign['id'] . "\n";
}
echo 'Ending with memory usage: ' . memory_get_usage(true) / 1024 / 1024 . " MB \n";
Output:
输出：
Starting with memory usage: 6 MB
Ending with memory usage: 109.46875 MB
Here, the disappointing getIterator()method:
在这里，令人失望的getIterator()方法：
namespace Doctrine\DBAL\Driver\Mysqli\MysqliStatement
/**
* {@inheritdoc}
*/
public function getIterator()
{
$data = $this->fetchAll();
return new \ArrayIterator($data);
}
You can use my little library to actuallystream heavy tables using PHP Doctrine or DQL or just pure SQL. However you find appropriate: https://github.com/EnchanterIO/remote-collection-stream
您可以使用我的小库实际使用 PHP Doctrine 或 DQL 或纯 SQL来流式传输重表。但是你觉得合适：https: //github.com/EnchanterIO/remote-collection-stream
