Example of how to use bind_result vs get_result

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)

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.

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