PHP $Stmt->Num_Rows Doesnt Work by Prepared Statements

PHP $stmt-num_rows doesnt work by prepared statements

You need to transfer the result set from the SELECT query before calling the ->num_rows() method.

// your code    

$stmt->execute();
$stmt->store_result();  
$numRows = $stmt->num_rows;

echo $numRows;

---

Here's the reference:

• http://php.net/manual/en/mysqli.store-result.php

PHP / mysqli: Prepared Statements with num_rows constantly returning nothing

The behaviour of mysqli_num_rows() depends on whether buffered or unbuffered result sets are being used. For unbuffered result sets, mysqli_num_rows() will not return the correct number of rows until all the rows in the result have been retrieved. Note that if the number of rows is greater than PHP_INT_MAX, the number will be returned as a string.

Also make sure that you declare ->store_result() first. Moreover the function doesn't work with LIMIT used jointly with SQL_CALC_FOUND_ROWS. If you want to obtain the total rows found you must do it manually.

EDIT:

If nothing from the suggestions does not work for you, then I would propose to rewrite your SQL query:

 SELECT `Name`, (SELECT COUNT(*) FROM `Persons`) AS `num_rows` FROM `Persons` WHERE `Name` LIKE ?

This query will return the total number from your Persons table, as well as Name, if exist.

PHP- mysqli-num_rows always returns 0, Prepared statements

1st : For update query You need to check affected_rows . not num_rows

2nd : After execute check like this $row_count= $stmt2->affected_rows; If query executed successfully but no changes in data means it will return 0 .

if($stmt2->execute()){

    echo $stmt2->affected_rows; 
 }

Affected_rows :

Affected_rows is for insert,update,delete

Num_rows :

Num_rows is for select

Mysqli Prepared Stmt returns 0 num_rows

While in my opinion the two given answers here are not correct, I think I know where the problem is.

Well first, as said, no need to assign values to variables before you bind them. Thats not true at all. It makes me quite angry, because I read this over and over here on stackoverflow... and its wrong. Simply. Wrong. If it would be true, you couldn't perform multiple prepared statements with different values. Even if its old and many people don't like to see it here, a olink from W3:
https://www.w3schools.com/php/php_mysql_prepared_statements.asp It shows that what you're trying is totally possible and it also shows the possibility that you have with prepared statements.

So, now to your problem:

What you're doing is totally fine. But there's another thing you're missing, and I think thats what cause the error. The missing store_result() function.

Give this code a chance and tell me if it works:

public function login(){
    $sql = "SELECT * FROM sys_usr WHERE uid = ? AND passwd = ?";
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param("ss", $usr,$pwd);

    $usr = $this->usr;
    $pwd = $this->pwd;

    $stmt->execute();
    $stmt->store_result(); // Quite sure you need this to perform a num_rows...
    echo  $stmt->num_rows;
}

num_rows doesn't work with prepared statement

in object oriented mysqli, num_rows is not a function, it's an attribute of the result (stmt). You need $stmt->num_rows; not $stmt->num_rows();

In your second example, you're not using (), you are doing it correctly, hence why it functions in the second but not the first.

$db = new mysqli("localhost", "root", "", "test");
$stmt = $db->prepare('SELECT unique_col FROM user WHERE username=?');
$stmt->bind_param('s', $userzzz);
$stmt->execute();
$stmt->store_result();
$rows = $stmt->num_rows;
if ($rows > 0){
echo "good";    
} else {
echo "bad";
}

I also added $stmt->store_result(). It is finicky and num_rows will be 0 unless you store the result before you run $stmt->num_rows;

I'd also use a unique column instead of *, such as id for example.

Prepared statements: num_rows is always zero

The problem is actually documented in the php doc for mysqli_stmt_num_rows

Returns the number of rows in the result set. The use of mysqli_stmt_num_rows() depends on whether or not you used mysqli_stmt_store_result() to buffer the entire result set in the statement handle.

So because the result has been not transferred to statement handle, you cannot calculate the rows properly. Thats why the solution is like you mentioned:

$stmt->execute();
$stmt->store_result();
$stmt->bind_result($itemname);
$numitems = $stmt->num_rows;

mysqli prepared statement num_rows returns 0 while query returns greater than 0

When you execute a statement through mysqli, the results are not actually in PHP until you fetch them -- the results are held by the DB engine. So the mysqli_stmt object has no way to know how many results there are immediately after execution.

Modify your code like so:

$stmt->execute();
$stmt->store_result(); // pull results into PHP memory

// now you can check $stmt->num_rows;

See the manual

This doesn't apply to your particular example, but if your result set is large, $stmt->store_result() will consume a lot of memory. In this case, if all you care about is figuring out whether at least one result was returned, don't store results; instead, just check whether the result metadata is not null:

$stmt->execute();
$hasResult = $stmt->result_metadata ? true : false;

See the manual

Cannot get the number of rows and fetch when using MySQLi prepared statement

If you want to use mysqli_stmt::$num_rows (that is, check the number of rows on the prepared statement), you need to use $stmt->store_result() after executing the prepared statement before being able to check the number of rows. That means that the result is stored into memory before we check how many rows was returned.

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss',$log_username,$log_username);
$stmt->execute();
$stmt->store_result(); // Need to store the result into memory first
if ($stmt->num_rows) {
    // ...

However, if you want to use mysqli_result::$num_rows (on the MySQLi-result you convert from the statement result), you need to do that after doing $result = $stmt->get_result();, and use $result->num_rows;, like shown below.

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss',$log_username,$log_username);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows) {
    while ($row = $result->fetch_assoc()) {
    // ....

In the end, they should both end up doing the same thing - provide a number of the rows returned by the original prepared query.

Note
It's important to note that you cannot use store_result() and get_result() on the same statement. Which means that in the first example, you can not convert to a mysqli-result object (by using get_result(), which allows you to use the standard fetch_assoc() method). As store_result() stores the result into memory, there are nothing for get_result() to convert, and vice-versa.

This means that if you use store_result(), you need to fetch through the statement-fetch, mysqli_stmt::fetch() and bind the results though mysqli_stmt::bind_result(). If you use get_result(), you should check the number of rows on the resulting MySQLi-result object (as shown in the second example).

You should therefor construct your code such that you only need to use one of them.

---

That being said, using affected_rows like suggested in the comments, isn't the right tool for the job - as per the manual on mysqli_stmt::$affected_rows (same thing applies for a regular query, mysqli::$affected_rows):

Returns the number of rows affected by INSERT, UPDATE, or DELETE query.

This function only works with queries which update a table. In order to get the number of rows from a SELECT query, use mysqli_stmt_num_rows() instead.

• PHP.net on mysqli_stmt::store_result()
• PHP.net on mysqli_stmt::get_result()
• PHP.net on mysqli_stmt::$num_rows

mysqli_stmt_num_rows with prepared statement doesn't return number of rows

As you have already correctly noticed you need mysqli_stmt_store_result($stmt); after mysqli_stmt_execute($stmt);.

As per PHP docs:

If you use mysqli_stmt_store_result(), mysqli_stmt_num_rows() may be called immediately.

However, I need to point out that you don't need it, or in fact you do not need to use mysqli_stmt_num_rows() at all. I don't think I ever had to use this function myself neither.

What you are trying to achieve is to check if a particular row exists in DB. This can be done as mentioned here: https://phpdelusions.net/mysqli/check_value

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli("localhost", "root", "", "test1");

$myUser = "qqq";

$sql = 'SELECT 1 FROM `users` WHERE `userName`=? LIMIT 1';
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $myUser);
$stmt->execute();
$exists = (bool) $stmt->get_result()->fetch_row();

die(nl2br("myUser = ".$myUser."\nmyresult = ".$exists));

Of course instead of (bool) $stmt->get_result()->fetch_row() you could use (bool) $stmt->get_result()->num_rows.

mysqli prepared statement num_rows function

After executing, you need to use mysqli_stmt_store_result() to get the result set, so it will know how many rows there are. Also, you should apply $num_rows to the statement, not the connection.

$stmt->execute();
$stmt->store_result();
$nrows1 = $stmt->num_rows;

---

Related Topics