php 如何使用 bind_result 与 get_result 的示例

Example of how to use bind_result vs get_result

提问by Arian Faurtosh

I would like to see an example of how to call using bind_resultvs. get_resultand what would be the purpose of using one over the other.

我想看一个例子，说明如何使用bind_resultvs.调用，get_result以及使用一个而不是另一个的目的是什么。

Also the pro and cons of using each.

还有使用每个的利弊。

What is the limitation of using either and is there a difference.

使用两者的限制是什么，有什么区别。

回答by Arian Faurtosh

The deciding factor for me, is whether I call my query columns using *.

对我而言，决定性因素是我是否使用*.

Using bind_result()would be better for this:

使用bind_result()会更好：

// Use bind_result() with fetch()
$query1 = 'SELECT id, first_name, last_name, username FROM table WHERE id = ?';

Using get_result()would be better for this:

使用get_result()会更好：

// Use get_result() with fetch_assoc() 
$query2 = 'SELECT * FROM table WHERE id = ?';

---

---

Example 1 for $query1using bind_result()

$query1使用示例 1bind_result()

$query1 = 'SELECT id, first_name, last_name, username FROM table WHERE id = ?';
$id = 5;

if($stmt = $mysqli->prepare($query)){
   /*
        Binds variables to prepared statement

        i    corresponding variable has type integer
        d    corresponding variable has type double
        s    corresponding variable has type string
        b    corresponding variable is a blob and will be sent in packets
   */
   $stmt->bind_param('i',$id);

   /* execute query */
   $stmt->execute();

   /* Store the result (to get properties) */
   $stmt->store_result();

   /* Get the number of rows */
   $num_of_rows = $stmt->num_rows;

   /* Bind the result to variables */
   $stmt->bind_result($id, $first_name, $last_name, $username);

   while ($stmt->fetch()) {
        echo 'ID: '.$id.'<br>';
        echo 'First Name: '.$first_name.'<br>';
        echo 'Last Name: '.$last_name.'<br>';
        echo 'Username: '.$username.'<br><br>';
   }

   /* free results */
   $stmt->free_result();

   /* close statement */
   $stmt->close();
}

/* close connection */
$mysqli->close();

Example 2 for $query2using get_result()

$query2使用示例 2get_result()

$query2 = 'SELECT * FROM table WHERE id = ?'; 
$id = 5;

if($stmt = $mysqli->prepare($query)){
   /*
        Binds variables to prepared statement

        i    corresponding variable has type integer
        d    corresponding variable has type double
        s    corresponding variable has type string
        b    corresponding variable is a blob and will be sent in packets
   */
   $stmt->bind_param('i',$id);

   /* execute query */
   $stmt->execute();

   /* Get the result */
   $result = $stmt->get_result();

   /* Get the number of rows */
   $num_of_rows = $result->num_rows;



   while ($row = $result->fetch_assoc()) {
        echo 'ID: '.$row['id'].'<br>';
        echo 'First Name: '.$row['first_name'].'<br>';
        echo 'Last Name: '.$row['last_name'].'<br>';
        echo 'Username: '.$row['username'].'<br><br>';
   }

   /* free results */
   $stmt->free_result();

   /* close statement */
   $stmt->close();
}

/* close connection */
$mysqli->close();

---

---

As you can see you can't use bind_resultwith *. However, get_resultworks for both, but bind_resultis simpler and takes out some of the mess with $row['name'].

如您所见，您不能使用bind_resultwith *。但是，get_result两者都适用，但bind_result更简单，并消除了$row['name'].

---

---

bind_result()

绑定结果（）

Pros:

优点：

• Simpler
• No need to mess with $row['name']
• Uses fetch()

• 更简单
• 没必要惹 $row['name']
• 用途 fetch()

Cons:

缺点：

• Doesn't work with SQL query that use *

• 不适用于使用的 SQL 查询 *

---

---

get_result()

获取结果（）

Pros:

优点：

• Works with all SQL statements
• Uses fetch_assoc()

• 适用于所有 SQL 语句
• 用途 fetch_assoc()

Cons:

缺点：

• Must mess around with array variables $row[]
• Not as neat
• requires MySQL native driver (mysqlnd)

• 必须弄乱数组变量 $row[]
• 没那么整洁
• 需要 MySQL 本机驱动程序 ( mysqlnd)

回答by Your Common Sense

Examples you can find on the respective manual pages.

您可以在相应的手册页上找到示例。

While pro and cons are quite simple:

虽然利弊很简单：

• get_result is the only sane way to handle results
• yet it is not always available and your code have to have a fallback using ugly bind_result.

• get_result 是处理结果的唯一合理方式
• 但它并不总是可用，您的代码必须使用丑陋的 bind_result 进行回退。

Anyway, if your idea is to use either function right in the application code - this idea is wrong. Yet as long as you have them encapsulated in some method to return your data from the query, it doesn't really matter, which one to use, save for the fact that you will need ten times more code to implement bind_result.

无论如何，如果您的想法是在应用程序代码中正确使用任一功能 - 这个想法是错误的。然而，只要您将它们封装在某种方法中以从查询中返回您的数据，使用哪个方法并不重要，因为您将需要十倍的代码来实现 bind_result。

回答by Norman Edance

Main difference I've noticed is that bind_result()gives you error 2014, when you try to code nested $stmt inside other $stmt, that is being fetched(without mysqli::store_result()):

我注意到的主要区别在于，当您尝试在其他 $stmt 中对嵌套的$stmt进行编码时，bind_result()会出现错误，即正在获取（没有）：2014mysqli::store_result()

Prepare failed: (2014) Commands out of sync; you can't run this command now

准备失败：（2014）命令不同步；你现在不能运行这个命令

Example:

例子：

• Function used in main code.

  function GetUserName($id)
  {
      global $conn;
  
      $sql = "SELECT name FROM users WHERE id = ?";
  
      if ($stmt = $conn->prepare($sql)) {
  
          $stmt->bind_param('i', $id);
          $stmt->execute();
          $stmt->bind_result($name);
  
          while ($stmt->fetch()) {
              return $name;
          }
          $stmt->close();
      } else {
          echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
      }
  }
  

• Main code.

  $sql = "SELECT from_id, to_id, content 
          FROM `direct_message` 
          WHERE `to_id` = ?";
  if ($stmt = $conn->prepare($sql)) {
  
      $stmt->bind_param('i', $myID);
  
      /* execute statement */
      $stmt->execute();
  
      /* bind result variables */
      $stmt->bind_result($from, $to, $text);
  
      /* fetch values */
      while ($stmt->fetch()) {
          echo "<li>";
              echo "<p>Message from: ".GetUserName($from)."</p>";
              echo "<p>Message content: ".$text."</p>";
          echo "</li>";
      }
  
      /* close statement */
      $stmt->close();
  } else {
      echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
  }
  

• 主代码中使用的函数。

  function GetUserName($id)
  {
      global $conn;
  
      $sql = "SELECT name FROM users WHERE id = ?";
  
      if ($stmt = $conn->prepare($sql)) {
  
          $stmt->bind_param('i', $id);
          $stmt->execute();
          $stmt->bind_result($name);
  
          while ($stmt->fetch()) {
              return $name;
          }
          $stmt->close();
      } else {
          echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
      }
  }
  

• 主要代码。

  $sql = "SELECT from_id, to_id, content 
          FROM `direct_message` 
          WHERE `to_id` = ?";
  if ($stmt = $conn->prepare($sql)) {
  
      $stmt->bind_param('i', $myID);
  
      /* execute statement */
      $stmt->execute();
  
      /* bind result variables */
      $stmt->bind_result($from, $to, $text);
  
      /* fetch values */
      while ($stmt->fetch()) {
          echo "<li>";
              echo "<p>Message from: ".GetUserName($from)."</p>";
              echo "<p>Message content: ".$text."</p>";
          echo "</li>";
      }
  
      /* close statement */
      $stmt->close();
  } else {
      echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
  }
  

回答by mti2935

get_result() is now only available in PHP by installing the MySQL native driver (mysqlnd). In some environments, it may not be possible or desirable to install mysqlnd.

get_result() 现在只能通过安装 MySQL 本机驱动程序 (mysqlnd) 在 PHP 中使用。在某些环境中，可能无法或不希望安装 mysqlnd。

Notwithstanding, you can still use mysqli to do 'select *' queries, and get the results with the field names - although it is slightly more complicated than using get_result(), and involves using php's call_user_func_array() function. See example at How to use bind_result() instead of get_result() in phpwhich does a simple 'select *' query, and outputs the results (with the column names) to an HTML table.

尽管如此，您仍然可以使用 mysqli 进行“select *”查询，并使用字段名称获取结果 - 尽管它比使用 get_result() 稍微复杂一些，并且涉及使用 php 的 call_user_func_array() 函数。请参阅如何在 php 中使用 bind_result() 而不是 get_result() 中的示例，它执行简单的“select *”查询，并将结果（带有列名）输出到 HTML 表。

回答by Coolen

I think example 2 will only work like this, because store_result and get_result both get the info from the table.

我认为示例 2 只能这样工作，因为 store_result 和 get_result 都从表中获取信息。

So remove

所以删除

/* Store the result (to get properties) */
$stmt->store_result();

And change the order a bit. This is the end result:

并稍微改变一下顺序。这是最终结果：

$query2 = 'SELECT * FROM table WHERE id = ?'; 
$id = 5;

if($stmt = $mysqli->prepare($query)){
 /*
    Binds variables to prepared statement

    i    corresponding variable has type integer
    d    corresponding variable has type double
    s    corresponding variable has type string
    b    corresponding variable is a blob and will be sent in packets
 */
$stmt->bind_param('i',$id);

/* execute query */
$stmt->execute();

/* Get the result */
$result = $stmt->get_result();

/* Get the number of rows */
$num_of_rows = $result->num_rows;

while ($row = $result->fetch_assoc()) {
    echo 'ID: '.$row['id'].'<br>';
    echo 'First Name: '.$row['first_name'].'<br>';
    echo 'Last Name: '.$row['last_name'].'<br>';
    echo 'Username: '.$row['username'].'<br><br>';
}

/* free results */
$stmt->free_result();