Differencebetween Get_Result() and Store_Result() in PHP

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.

What is the difference between mysqli_stmt_get_result and mysqli_stmt_store_result?

mysqli_stmt_store_result() transfers all the rows of the result set into PHP memory. Subsequently "fetching" rows is just iterating over the in-memory result set. This is fine unless your result set is too large; it might exceed your PHP memory limit. This function returns only TRUE or FALSE. You can call it from your mysqli object or from a statement object.

mysqli_use_result() does not transfer the whole result set. It makes a separate call to the MySQL server each time you fetch a row. This way your PHP memory doesn't have to hold the entire result set at once. You can process each row and discard it. This is useful when the result set is large. This function returns a mysqli_result resource. You can call it only from the mysqli object, not from a statement object, so it can't be used for prepared statements.

Note that you cannot get the number of rows in a result set before the client (PHP) has fetched all the rows. So trying to read mysqli_stmt_num_rows() before fetching returns 0, even though you know the result should have something in it. Using the store-result functions counts as fetching the result set for this purpose.

mysqli_stmt_get_result() is a newer method in the mysqlnd driver. This function returns a mysqli_result resource from a prepared statement. This helps make it easier to code your fetch loop without using mysqli_stmt_bind_result(), which some people find confusing.

mysqli_stmt_get_result() always calls store-result internally, so you better have enough memory in PHP to hold the full result set. Given this, I don't know of any way to do row-by-row fetching if you use a prepared statement. You just have to make sure you don't run SQL queries that have large result sets.

Honestly, I avoid mysqli when I work with PHP. I prefer PDO. Lots of usage is cleaner and less confusing. I wish PHP had just deprecated mysqli at the same time that they deprecated mysql. Or at least made a clear statement that PDO was preferred.

store_result() and get_result() in mysql returns false

Use get_result() instead of store_result(), and then use the result object's num_rows:

$a->execute();
$res = $a->get_result();
if ($res->num_rows > 0) {
    while ($row = $res->fetch_assoc()) {
        $results[] = $row;
    }
    return $results;
} else {
    return false;
}

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)

Include column names in query results or query comparison

Use fetch_assoc() instead of fetch_row(). It will return an associative array. Then use foreach loop and the associative key to loop the array and compare the elements.

Also, remove store_result(), you already used get_result().

Fixed code would look like this:

$stmt = $conn->prepare("SELECT * FROM locations WHERE vstId = ?");
$stmt->bind_param("s", $vstId);
$stmt->execute();
$resultBefore = $stmt->get_result()->fetch_assoc();

// some event

$stmt = $conn->prepare("SELECT * FROM locations WHERE vstId = ?");
$stmt->bind_param("s", $vstId);
$stmt->execute();
$resultAfter = $stmt->get_result()->fetch_assoc();

foreach ($resultAfter as $col_name => $value) {
    if ($value !== $resultBefore[$col_name]) {
        $diff .= '<tr><td>' . htmlentities($col_name) . '</td><td>' . htmlentities($resultBefore[$col_name]) . '</td><td>' . htmlentities($value) . '</td></tr>';
    }
}
echo '<table>' . $diff . '</table>';

mysqli_store_result() vs. mysqli_use_result()

mysqli::store_result() will fetch the whole resultset from the MySQL server while mysqli::use_result() will fetch the rows one by one.

This is also mentioned in the mysqli::use_result docs you linked to:

The mysqli_use_result() function does not transfer the entire result set from the database and hence cannot be used functions such as mysqli_data_seek() to move to a particular row within the set. To use this functionality, the result set must be stored using mysqli_store_result(). One should not use mysqli_use_result() if a lot of processing on the client side is performed, since this will tie up the server and prevent other threads from updating any tables from which the data is being fetched.

You can usually always use mysqli::store_result() unless you have a good reason for not reading all rows from the server at once.

---

Related Topics