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

Question

This is my code. I am checking if a user exists or not in a login/registration system:

public function userExist($email){
    $stmt = $this->conn->prepare("select email from users where email= ?");
    $stmt->bind_param("s", $email);
    $stmt->execute();
    $stmt->store_result();
    if($stmt->num_rows>0){

    }
    else{

    }
}

Can I use get_result instead of store_result() ?

Answer 1

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.

Answer 2

As usual, the accepted answer is too localized, being much more focused on the insignificant details than on the answer itself.

There is also, sadly, a deleted answer, which, although being laconic, makes a perfect rule of thumb:

Use get_result whenever possible and store_result elsewhere.

Both functions load the pending resultset from the database to PHP process' memory, with get_result() being much more versatile and therefore preferred.

The only difference between get_result() and store_result() is that the former gets you a familiar mysqli_result resource/object which can be used to fetch the data using familiar fetch_row() / fetch_assoc() routines, as well as a slightly modern fetch_all(), all of those being incomparably more convenient than bind_result() routine which is the only option with store_result().

It could be noted that get_result() is only available when mysqlnd driver is used, which is not an issue in 2019 and beyond. If it's not available, you are probably to tick some checkbox in your shared host's configuration.