Store_Result() and Get_Result() in MySQL Returns False

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;
}

num_rows: get_result vs store_result

Looks like the other answer is wrong and get_result doesn't change the state of a statement (which is quite logical, as you are asking for mysqli_result and therefore supposedly going to work with it from now on).

You see, the way you are using get_result is quite pointless. To make any point of it, you have to assign the result to a variable, which will give you the desired outcome:

$res = $stmt->get_result();
echo $res->num_rows;

Note that the num_rows property is quite useless in general. If you want to know whether your query returned any data or not, just collect the rows into array and then you use this array for the purpose.

$data = $stmt->get_result()->fetch_all();
if ($data) {
    // whatever
} else {
    // oops!
}

And of course you shouldn't use such a query only to count the goods available in a category. For such a purpose a count(*) query have to be used.

Store_result and get_result for statement

Use get_result whenever possible and store_result elsewhere.

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.

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

PHP When and why do I need to call store_result()?

The reason why you have to call store_result() is because prepared statements in mysqli by default operate in unbuffered mode.

This is contrary to the information presented in the docs. You have to keep in mind that mysqli was developed as a shim rather than a proper DB library. MySQLi is full of bugs and suffers from a lot of bad decisions. One of them is unbuffered mode for prepared statements.

In fact you do not have to use store_result() at all. This function is not very useful, because you still need to bind the results to variables and fetch each row individually. A much better option is get_result().

$age = 3;
$sql = 'SELECT name FROM users WHERE age = ?';
$stmt->prepare($sql);
$stmt->bind_param('i', $age);
$stmt->execute();
$result = $stmt->get_result();

This is 4 lines compared to query() method, but the end result is the same. $result contains an instance of mysqli_result class.

You could write your own wrapper for it to make your life simpler. Here is an example how you could turn it into a single line with a wrapper function.

function mysqli_prepared_query(mysqli $mysqli, string $sql, array $params): mysqli_result
{
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    $stmt->execute();
    return $stmt->get_result();
}

$result =  mysqli_prepared_query($mysqli, 'SELECT ?,?', [1, 2]);

Of course the best solution would be to switch to PDO or use some kind of abstraction library, so that you do not have to deal with these confusing methods.

get_result()-fetch_assoc() returns 0 even thought num rows is 2

This gives you the logical AND of the values as one result:

select senderID AND headline AND message

You want this:

select senderID, headline, message

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.

PHP & MYSQL Fatal Error: Call to a member function fetch_assoc() on boolean

I was able to get it to work finally. @bio_sprite - thanks for the hint of binding the results. Here is the code that I used:

    $friends = array(); 
    $i = 0;
    $stmt1 = $this->conn->prepare("SELECT User1, User2 FROM friends WHERE User1 = ? OR User2 = ?"); 
    $stmt1->bind_param("ss", $myuid, $myuid);

    if ($stmt1->execute()) {
        $stmt1->bind_result($User1, $User2);

        while($stmt1->fetch()){

            if ($User1 != $myuid) {
                $friends[$i]= $User1;
                $i++;
            } else if ($User2 != $myuid){
                $friends[$i]= $User2;
                $i++;
                }
        }

        $stmt1->close();

        return $friends;

Why is store_result() not returning num_rows equivalent to mysqli Regular Statement?

I'm certain this isn't the most efficient way to do things, but it's all I have on it; decided to break the statement up.

First I did a statement for the count:

    $query_count = $this->con->stmt_init();
    $query_count->prepare('SELECT COUNT(*) FROM notifications WHERE user_to=?');
    $query_count->bind_param('s', $userLoggedIn);
    $query_count->execute();

    $query_count_result = $query_count->get_result();
    $rows_count = $query_count_result->fetch_array();

    $qty = array_shift($rows_count);

Then I did a statement for the data:

        $query = $this->con->stmt_init();
        $query->prepare('SELECT * FROM notifications WHERE user_to=? ORDER BY id DESC');
        $query->bind_param('s', $userLoggedIn);
        $query->execute();

        $query_result = $query->get_result();

It solved my problem & everything works as expected. I know there is a better way to do this, so if someone has that suggestion great. In the meantime this will have to do.

---

Related Topics