Example of how to use bind_result vs get_result

Question

I would like to see an example of how to call using bind_result vs. get_result and what would be the purpose of using one over the other.

Also the pro and cons of using each.

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

Answer 1

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

Using bind_result() would be better for this:

// 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:

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

---

Example 1 for $query1 using bind_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 $query2 using get_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_result with *. However, get_result works for both, but bind_result is simpler and takes out some of the mess with $row['name'].

---

bind_result()

Pros:

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

Cons:

• Doesn't work with SQL query that use *

---

get_result()

Pros:

• Works with all SQL statements
• Uses fetch_assoc()

Cons:

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

Answer 2

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.

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.

Answer 3

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() ):

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

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;
  }
  

Answer 4

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.

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 php which does a simple 'select *' query, and outputs the results (with the column names) to an HTML table.

Answer 5

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

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();