php 获取带有 mysqli 结果的行数组

get array of rows with mysqli result

提问by Yaniv Golan

I need to get all the rows from result object. I'm trying to build a new array that will hold all rows.

我需要从结果对象中获取所有行。我正在尝试构建一个包含所有行的新数组。

Here is my code:

这是我的代码：

$sql = new mysqli($config['host'],$config['user'],$config['pass'],$config['db_name']);
if (mysqli_connect_errno())
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$query = "SELECT domain FROM services";
$result = $sql->query($query);           
while($row = $result->fetch_row());
{
    $rows[]=$row;
}
$result->close();
$sql->close();
return $rows;

$rowsis supposed to be the new array that contains all, rows but instead I get an empty array.

$rows应该是包含所有行的新数组，但我得到了一个空数组。

Any ideas why this is happening?

任何想法为什么会发生这种情况？

回答by cletus

You had a slight syntax problem, namely an errant semi-colon.

您有一个轻微的语法问题，即错误的分号。

while($row = $result->fetch_row());

Notice the semi-colon at the end? It means the block following wasn't executed in a loop. Get rid of that and it should work.

注意到最后的分号了吗？这意味着后面的块没有在循环中执行。摆脱它，它应该可以工作。

Also, you may want to check that the query actually works:

此外，您可能需要检查查询是否实际有效：

$sql = new mysqli($config['host'], $config['user'], $config['pass'], $config['db_name']);
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit;
}
$query = "SELECT domain FROM services";
$result = $sql->query($query);     
if (!$result) {
  printf("Query failed: %s\n", $mysqli->error);
  exit;
}      
while($row = $result->fetch_row()) {
  $rows[]=$row;
}
$result->close();
$sql->close();
return $rows;

回答by Your Common Sense

Newest versions of mysqli have some improvements that can simplify such a task.

最新版本的 mysqli 有一些改进可以简化这样的任务。

First, of all, there is a useful function to return an array with all rows returned by a query, mysqli_fetch_all()
It means in case you need a simple enumerated array, the code would be much simpler:

首先，有一个有用的函数可以返回一个包含查询返回的所有行的数组， 这意味着如果您需要一个简单的枚举数组，代码会简单得多：mysqli_fetch_all()

$query = "SELECT domain FROM services";
$result = $sql->query($query);     
return $result->fetch_all(MYSQLI_ASSOC);

or even all in one line,

甚至都在一行中，

return $sql->query("SELECT domain FROM services")->fetch_all(MYSQLI_ASSOC);

However, if you need to use some column to index the resulting array, you still need a while loop like this:

但是，如果您需要使用某些列来索引结果数组，您仍然需要像这样的 while 循环：

$query = "SELECT domain FROM services";
$result = $sql->query($query);
$data = [];
while($row = $result->fetch_assoc()) {
  $data[$row['id']]=$row;
}

Note that you should always initialize an array before filling it up, because such a variable could already exist.

请注意，您应该始终在填充数组之前对其进行初始化，因为这样的变量可能已经存在。

Also, mysqli_resultclass is now Traversable. It means you can use it in the foreach loop right away, as though it's an array contains all rows from the database:

此外，mysqli_result类现在是可遍历的。这意味着您可以立即在 foreach 循环中使用它，就好像它是一个包含数据库中所有行的数组：

$query = "SELECT domain FROM services";
$result = $sql->query($query);
foreach($result as $row) {
    echo $row['name'];
}

But it is actually just a syntax sugar for the while loop - you cannot access values of this "array" directly, which makes this feature of a little use actually.

但它实际上只是 while 循环的语法糖——你不能直接访问这个“数组”的值，这使得这个特性实际上有点用处。

Obligatory notes.

强制性笔记。

This question is a decade old, and the way a connection is made and the query is performed, both in the question and the accepted answer, are obsoleted and frowned upon nowadays.

这个问题已有十年历史，并且在问题和接受的答案中建立连接和执行查询的方式如今已过时且不受欢迎。

When a connection is made, there are several things to keep in mind. I wrote an article on how to connect with mysqli properlythat provides a correct connection example emphasizing on the following issues:

建立连接时，需要记住几件事情。我写了一篇关于如何正确连接mysqli的文章，提供了一个正确的连接示例，强调了以下问题：

• a proper error reporting modemust be set
• a proper character setmust be set
• no manual error reportingcode should be ever used (like die(mysqli_connect_error()))
• a connection has to be made only once, in a separate file, and then just included into every script that needs a database interaction. in case a database code is used in a function, a connection variable must be passed in as a function parameter.

• 必须设置正确的错误报告模式
• 必须设置正确的字符集
• 不应使用手动错误报告代码（例如die(mysqli_connect_error())）
• 一个连接只需要在一个单独的文件中建立一次，然后就包含在每个需要数据库交互的脚本中。如果函数中使用了数据库代码，则必须将连接变量作为函数参数传入。

When it goes to running a query, there are several things to keep in mind as well:

在运行查询时，还需要记住以下几点：

• when even a single variable is used in the query, a prepared statementmustbe used instead of mysqli_query()
• as a result, a special function called mysqli_stmt_get_result()should be used in order to use familiar fetch functions to get the resulting rows. In case this function is not available you are probably to tick some checkbox in your cpanel (look for one labeled mysqlnd).
• given a prepared statement with mysqli, although being obligatory, takes a lot code to write, it is advised to use a helper function for mysqlithat would perform most of work automatically and make a mysqli prepared statement a smooth as a regular query.
• no manual error reportingcode should be ever used (like die(mysqli_error())). Thanks to the proper error mode, mysqli will report all errors automatically.

• 即使在查询中使用单个变量，也必须使用准备好的语句而不是mysqli_query()
• 因此，mysqli_stmt_get_result()应该使用一个特殊的函数调用，以便使用熟悉的提取函数来获取结果行。如果此功能不可用，您可能需要勾选 cpanel 中的某个复选框（查找标记为 的复选框mysqlnd）。
• 给定一个使用 mysqli 的预准备语句，虽然是强制性的，需要编写大量代码，但建议使用mysqli的辅助函数，该函数将自动执行大部分工作并使 mysqli 预准备语句像常规查询一样流畅。
• 不应使用手动错误报告代码（如die(mysqli_error())）。由于正确的错误模式，mysqli 将自动报告所有错误。