Example of How to Use Bind_Result VS Get_Result

Example of how to use bind_result vs get_result

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)

How to use bind_result() instead of get_result() in php

Assuming you can't use get_result() and you want an array of devices, you could do:

public function getAllDevices($user_id) {
    $stmt = $this->conn->prepare("SELECT device_id, device_name, device_info FROM devices WHERE  primary_owner_id = ?");
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $stmt->bind_result($id, $name, $info);
    $devices = array();

    while($stmt->fetch()) {
        $tmp = array();
        $tmp["id"] = $id;
        $tmp["name"] = $name;
        $tmp["info"] = $info;
        array_push($devices, $tmp);
    }
    $stmt->close();
    return $devices;
}

This creates a temporary array and stores the data from each row in it, and then pushes it to the main array. As far as I'm aware, you can't use SELECT * in bind_result(). Instead, you will annoyingly have to type out all the fields you want after SELECT

Use bind_result & fetch () or store_result() instead get_result

First of all, I find this approach utterly useless. What are you actually doing is dismembering fine SQL sentence into some anonymous parts.

"SELECT * FROM post WHERE post_category=?"

looks WAY better than your anonymous parameters of which noone have an idea.

'post','post_category=?'

One can tell at glance what does first statement to do. and have no idea on the second. Not to mention it's extreme:

'post','post_category=?',NULL, NULL, 'username, password'

So, instead of this kindergarten query builder I would rather suggest a function that accepts only two parameters - a query itself and array with bound data:

$myresult = Select("SELECT * FROM post WHERE post_category=?", [2]);

To make it more useful, I wouild make separate functions to get different result types, making your second line with fetch_object() obsolete (however, speaking of objects, they are totally useless to represent a table row). Example:

$row = $db->selectRow("SELECT * FROM post WHERE post_category=?", [2]);

Look: it's concise yet readable!

As a further step you may wish to implement more placeholder types, to allow fields for ORDER BY clause be parameterized as well:

$data = $db->getAll('id','SELECT * FROM t WHERE id IN (?a) ORDER BY ?n', [1,2],'f');

you can see how it works, as well as other functions and use cases in my safeMysql library

Prepared statement retrieving result: Why does bind_result() work and get_result() doesn't?

I finally found the problem. Through this post: Example of how to use bind_result vs get_result
and the comment of @Coolen.
In the get_result version the line: $stmt->store_result();
should be removed!

What is the difference between get_result() and store_result() in php?

It depends on how you plan to read the result set. But in the actual example you have given, you are not interested in reading any returned data. The only thing that interests you is whether there is a record or not.

In that case your code is fine, but it would work equally well with get_result.

The difference becomes more apparent, when you want to get for example the userid of the user with the given email:

SELECT id FROM users WHERE email = ?

If you plan to read out that id with $stmt->fetch, then you would stick to
store_result, and would use bind_result to define in which variable you want to get this id, like this:

$stmt->store_result();    
$stmt->bind_result($userid);  // number of arguments must match columns in SELECT
if($stmt->num_rows > 0) {
    while ($stmt->fetch()) {
        echo $userid;  
    }
}

If you prefer to get a result object on which you can call fetch_assoc() or any of the fetch_* variant methods, then you need to use get_result, like this:

$result = $stmt->get_result();   // You get a result object now
if($result->num_rows > 0) {     // Note: change to $result->...!
    while ($data = $result->fetch_assoc()) {
        echo $data['id'];
    }
}

Note that you get a result object from get_result, which is not the case with store_result. You should get num_rows from that result object now.

Both ways work, and it is really a matter of personal preference.

---

Related Topics