Example of how to use bind_result vs get_result

Solution 1:

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)

Solution 2:

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".

Solution 3:

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 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.