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.
The deciding factor for me, is whether I call my query columns 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 = ?';
get_result()
would be better for this:// Use get_result() with fetch_assoc()
$query2 = 'SELECT * FROM table WHERE id = ?';
$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();
$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']
.
Pros:
$row['name']
fetch()
Cons:
*
Pros:
fetch_assoc()
Cons:
$row[]