php - 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)