php MySQLI 准备好的语句：num_rows & fetch_assoc

MySQLI Prepared Statement: num_rows & fetch_assoc

提问by Arbiter

Below is some poorly written and heavily misunderstood PHP code with no error checking. To be honest, I'm struggling a little getting my head around the maze of PHP->MySQLi functions! Could someone please provide an example of how one would use prepared statements to collect results in an associative array whilst also getting a row count from $stmt? The code below is what I'm playing around with. I think the bit that's throwing me off is using $stmtvalues after store_resultand then trying to collect an assoc array, and I'm not too sure why...

下面是一些写得不好且被严重误解的 PHP 代码，没有错误检查。老实说，我在 PHP->MySQLi 函数的迷宫中有点挣扎！有人可以提供一个示例，说明如何使用准备好的语句在关联数组中收集结果，同时还从 $stmt 获取行数？下面的代码是我正在玩的。我认为让我失望的一点是在使用$stmt值之后store_result然后尝试收集一个关联数组，我不太确定为什么......

$mysqli = mysqli_connect($config['host'], $config['user'], $config['pass'], $config['db']);
$stmt = $mysqli->prepare("SELECT * FROM licences WHERE generated = ?");
$stmt->bind_param('i', $core['id']);
$result = $stmt->execute();
$stmt->store_result();

if ($stmt->num_rows >= "1") {

    while($data = $result->fetch_assoc()){ 
        //Loop through results here $data[] 
    }

}else{

    echo "0 records found";

}

I feel a little cheeky just asking for code, but its a working demonstration of my circumstances that I feel I need to finally understand what's actually going on. Thanks a million!

我只是要求代码有点厚颜无耻，但它是对我的情况的有效演示，我觉得我需要最终了解实际发生的事情。太感谢了！

采纳答案by Kjeld Schmidt

True, the Databasefunctions are a bit weird. You'll get there.

确实，数据库函数有点奇怪。你会到达那里。

The code looks a bit iffy, but heres how it works:

代码看起来有点不确定，但它是如何工作的：

A connection is build, a statement prepared, a parameter bound and it's executed, all well.

建立连接、准备语句、绑定参数并执行，一切顺利。

$result = $stmt->execute(); //execute() tries to fetch a result set. Returns true on succes, false on failure.
$stmt->store_result(); //store_result() "binds" the last given answer to the statement-object for... reasons. Now we can use it!

if ($stmt->num_rows >= "1") { //Uses the stored result and counts the rows.

  while($data = $result->fetch_assoc()){ 
        //And here, the answer-object is turned into an array-(object)
        // which can be worked with nicely.
        //It loops trough all entries in the array.
  }

}else{

   echo "0 records found";
}

回答by Blaztix

I searched for a long time but never found documentation needed to respond correctly, but I did my research.

我搜索了很长时间，但从未找到正确响应所需的文档，但我做了我的研究。

$stmt->get_result()replace $stmt->store_result()for this purpose. So, If we see

$stmt->get_result()$stmt->store_result()为此目的更换。所以，如果我们看到

$stmt_result = $stmt->get_result();
var_dump($stmt_result);

we get

我们得到

object(mysqli_result)[3]
  public 'current_field' => int 0
  public 'field_count' => int 10
  public 'lengths' => null
  public 'num_rows' => int 8  #That we need!
  public 'type' => int 0

Therefore I propose the following generic solution. (I include the bug report I use)

因此，我提出以下通用解决方案。（我包括我使用的错误报告）

#Prepare stmt or reports errors
($stmt = $mysqli->prepare($query)) or trigger_error($mysqli->error, E_USER_ERROR);

#Execute stmt or reports errors
$stmt->execute() or trigger_error($stmt->error, E_USER_ERROR);

#Save data or reports errors
($stmt_result = $stmt->get_result()) or trigger_error($stmt->error, E_USER_ERROR);

#Check if are rows in query
if ($stmt_result->num_rows>0) {

  # Save in $row_data[] all columns of query
  while($row_data = $stmt_result->fetch_assoc()) {
    # Action to do
    echo $row_data['my_db_column_name_or_ALIAS'];
  }

} else {
  # No data actions
  echo 'No data here :(';
}
$stmt->close();

回答by Feroz Ahmad

$result = $stmt->execute(); /* function returns a bool value */

reference : http://php.net/manual/en/mysqli-stmt.execute.php

参考：http: //php.net/manual/en/mysqli-stmt.execute.php

so its just sufficient to write $stmt->execute();for the query execution.

所以它足以$stmt->execute();为查询执行编写代码。

---

---

The basic idea is to follow the following sequence:
1. make a connection. (now while using sqli or PDO method you make connection and connect with database in a single step)
2. prepare the query template
3. bind the the parameters with the variable
4. (set the values for the variable if not set or if you wish to change the values) and then Execute your query.
5. Now fetch your data and do your work.
6. Close the connection.

基本思想是遵循以下顺序：
1. 建立连接。（现在，在使用 sqli 或 PDO 方法时，您只需一步即可建立连接并与数据库连接）
2. 准备查询模板
3. 将参数与变量绑定
4. （如果未设置，请设置变量的值，或者如果您希望更改值），然后执行您的查询。
5. 现在获取您的数据并完成您的工作。
6. 关闭连接。

---

---

/*STEP 1*/
$mysqli = mysqli_connect($servername,$usrname,$pswd,$dbname);
/*STEP 2*/
$stmt = $mysqli->prepare("SELECT * FROM licences WHERE generated = ?");
/*Prepares the SQL query, and returns a statement handle to be used for further operations on the statement.*/
//mysqli_prepare() returns a statement object(of class mysqli_stmt) or FALSE if an error occurred.
/* STEP 3*/
$stmt->bind_param('i', $core['id']);//Binds variables to a prepared statement as parameters
/* STEP 4*/
$result = $stmt->execute();//Executes a prepared Query
/* IF you wish to count the no. of rows only then you will require the following 2 lines */
$stmt->store_result();//Transfers a result set from a prepared statement
$count=$stmt->num_rows;
/*STEP 5*/
//The best way is to bind result, its easy and sleek
while($data = $stmt->fetch()) //use fetch() fetch_assoc() is not a member of mysqli_stmt class
{ //DO what you wish
  //$data is an array, one can access the contents like $data['attributeName']
}

One must call mysqli_stmt_store_result() for (SELECT, SHOW, DESCRIBE, EXPLAIN), if one wants to buffer the complete result set by the client, so that the subsequent mysqli_stmt_fetch() call returns buffered data.
It is unnecessary to call mysqli_stmt_store_result() for other queries, but if you do, it will not harm or cause any notable performance in all cases.
--reference: php.net/manual/en/mysqli-stmt.store-result.php
and http://www.w3schools.com/php/php_mysql_prepared_statements.asp
One must look up the above reference who are facing issue regarding this, My answer may not be perfect, people are welcome to improve my answer...

必须调用 mysqli_stmt_store_result() for (SELECT, SHOW, DESCRIBE, EXPLAIN)，如果要缓冲客户端设置的完整结果，以便后续的 mysqli_stmt_fetch() 调用返回缓冲数据。
没有必要为其他查询调用 mysqli_stmt_store_result()，但如果你这样做了，它不会在所有情况下损害或导致任何显着的性能。
--reference: php.net/manual/en/mysqli-stmt.store-result.php
和http://www.w3schools.com/php/php_mysql_prepared_statements.asp
一个人必须查找上面的参考谁面临这方面的问题, 我的回答可能不完美，欢迎大家改进我的回答...

回答by PaulJ

Your problem here is that to do a fetch->assoc(), you need to get first a result set from a prepared statement using:

您的问题是，要执行 a fetch->assoc()，您需要首先使用以下方法从准备好的语句中获取结果集：

http://php.net/manual/en/mysqli-stmt.get-result.php

http://php.net/manual/en/mysqli-stmt.get-result.php

And guess what: this function only works if you are using MySQL native driver, or "mysqlnd". If you are not using it, you'll get the "Fatal error" message.

猜猜看：此功能仅在您使用 MySQL 本机驱动程序或“mysqlnd”时才有效。如果您不使用它，您将收到“致命错误”消息。

回答by yovsky

You can try this using the mysqli_stmt function get_result() which you can use to fetch an associated array. Note get_result returns an object of type mysqli_result.

您可以尝试使用 mysqli_stmt 函数 get_result() 来获取关联数组。注意 get_result 返回一个 mysqli_result 类型的对象。

$stmt->execute();
$result = $stmt->get_result(); //$result is of type mysqli_result
$num_rows = $result->num_rows;  //count number of rows in the result

// the '=' in the if statement is intentional, it will return true on success or false if it fails.
if ($result_array = $result->fetch_assoc(MYSQLI_ASSOC)) { 
       //loop through the result_array fetching rows.
       // $ rows is an array populated with all the rows with an associative array with column names as the key 
        for($j=0;$j<$num_rows;$j++)
            $rows[$j]=$result->fetch_row();
        var_dump($rows);
   }
else{
   echo 'Failed to retrieve rows';
}

回答by Dharman

If you would like to collect mysqli results into an associative array in PHP you can use fetch_all()method. Of course before you try to fetch the rows, you need to get the result with get_result(). execute()does not return any useful values.

如果您想将 mysqli 结果收集到 PHP 中的关联数组中，您可以使用fetch_all()方法。当然，在尝试获取行之前，您需要使用get_result(). execute()不返回任何有用的值。

For example:

例如：

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($config['host'], $config['user'], $config['pass'], $config['db']);
$mysqli->set_charset('utf8mb4'); // Don't forget to set the charset!

$stmt = $mysqli->prepare("SELECT * FROM licences WHERE generated = ?");
$stmt->bind_param('i', $core['id']);
$stmt->execute(); // This doesn't return any useful value
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

if ($data) {
    foreach ($data as $row) {
        //Loop through results here
    }
} else {
    echo "0 records found";
}

I am not sure why would you need num_rows, you can always use the array itself to check if there are any rows. An empty array is false-ish in PHP.

我不确定你为什么需要num_rows，你总是可以使用数组本身来检查是否有任何行。空数组在 PHP 中是假的。