Example of how to use bind_result vs get_result

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.

Although both methods work with * queries, when bind_result() is used, the columns are usually listed explicitly in the query, so one can consult the list when assigning returned values in bind_result(), because the order of variables must strictly match the structure of the returned row.

Example 1 for $query1 using bind_result()

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

$stmt = $mysqli->prepare($query1);
/*
    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>';
}

Example 2 for $query2 using get_result()

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

$stmt = $mysqli->prepare($query2);
/*
    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>';
}

---

bind_result()

Pros:

• Works with outdated PHP versions
• Returns separate variables

Cons:

• All variables have to be listed manually
• Requires more code to return the row as array
• The code must be updated every time when the table structure is changed

---

get_result()

Pros:

• Returns associative/enumerated array or object, automatically filled with data from the returned row
• Allows fetch_all() method to return all returned rows at once

Cons:

• requires MySQL native driver (mysqlnd)

Examples you can find on the respective manual pages, get_result() and bind_result().

While pros and cons are quite simple:

• get_result() is the only sane way to handle results
• yet it could be not always available on some outdated and unsupported PHP version

In a modern web application the data is never displayed right off the query. The data has to be collected first and only then output has to be started. Or even if you don't follow the best practices, there are cases when the data has to be returned, not printed right away.

Keeping that in mind let's see how to write a code that returns the selected data as a nested array of associative arrays using both methods.

bind_result()

$query1 = 'SELECT id, first_name, last_name, username FROM `table` WHERE id = ?';
$stmt = $mysqli->prepare($query1);
$stmt->bind_param('s',$id);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($id, $first_name, $last_name, $username);
$rows = [];
while ($stmt->fetch()) {
    $rows[] = [
        'id' => $id,
        'first_name' => $first_name,
        'last_name' => $last_name,
        'username' => $username,
    ];
}

and remember to edit this code every time a column is added or removed from the table.

get_result()

$query2 = 'SELECT * FROM `table` WHERE id = ?';
$stmt = $mysqli->prepare($query2);
$stmt->bind_param('s', $id);
$stmt->execute();
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

and this code remains the same when the table structure is changed.

And there's more.
In case you decide to automate the boring routine of preparing/binding/executing into a neat function that would be called like this

$query = 'SELECT * FROM `table` WHERE id = ?';
$rows = prepared_select($query, [$id])->fetch_all(MYSQLI_ASSOC);

with get_result() it will be quite a plausible task, a matter of just a few lines. But with bind_param() it will will be a tedious quest.

That's why I call the bind_result() method "ugly".

get_result() is 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 https://stackoverflow.com/questions/22823284/how-to-use-bind-result-instead-of-get-result-in-php/38491087#38491087 which does a simple SELECT * query and outputs the results (with the column names) to an HTML table.

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