Mysqli Prepared Statement Num_Rows Returns 0 While Query Returns Greater Than 0

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

Why does mysqli num_rows always return 0?

You need to call mysqli_stmt::store_result() prior to the num_rows lookup:

if($stmt = $mysqli->prepare("SELECT id, title, visible, parent_id FROM content WHERE parent_id = ? ORDER BY page_order ASC;")){  
    $stmt->bind_param('s', $data->id);  
    $stmt->execute();
    $stmt->store_result(); <-- This needs to be called here!
    $num_of_rows = $stmt->num_rows;  
    $stmt->bind_result($child_id, $child_title, $child_visible, $child_parent);  

    while($stmt->fetch()){
        //code
    }

    echo($num_of_rows);

    $stmt->close();
}

See the docs on mysqli_stmt::num_rows, it says it right near the top of the page (in the main description block).

num_rows with prepared statement returns 0 rows when equivalent normal query returns more

The documentation for mysqli_stmt::num_rows misses some detailed information about using num_rows with prepared statements. The description is rather ambiguous in that it refers only to the need to store the result when using the procedural style, but the object-oriented example makes it clear that you need to call the store_result() method before accessing the num_rows property. This means your code should be something like this:

$stmt = $conn->prepare("SELECT `totalhits`, `totalmisses`, `date` FROM `performance` WHERE `domain` = ? AND `profileid` = ? ORDER BY `date` DESC");
$domain = 'test';
$profileid = 1;
$stmt->bind_param('si', $domain,$profileid);
$stmt->execute();
$stmt->store_result();
echo $stmt->num_rows; //should now output 3

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() always returns 0 when using prepared statements

As mentioned by @Ghost below the issue I was missing was 1 line of code.

 $stmt->store_result();
 $rowCount = $stmt->num_rows;

This has to be called BEFORE I can access num_rows() on a prepared statement.

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

why is $stmt - num_rows returning 0 when login exists?

as mentioned ditch out, my_num_rows, and store_result, below works for me.

$email = $_POST['email'];
$password = $_POST['password'];
$arr = array();
$stmt = $db->prepare("SELECT email, password FROM users where email = :email 
and password = :password");
$stmt->bindParam(":email", $password);
$stmt->bindParam(":password", $password);

$stmt->execute();
$arr = $stmt->fetchAll();
if(!$arr) exit('No rows');
print_r($arr);
$stmt = null;

when executing $stmt-num_rows return 0

change your execution order as below:

$user_id = "CCD_00005";

    /* create a prepared statement */
    if ($stmt = mysqli_prepare($GLOBALS['conn'], "SELECT user_name FROM user_master WHERE user_id=?")) {

        /* bind parameters for markers */
        mysqli_stmt_bind_param($stmt, "s", $user_id);

        /* execute query */
        mysqli_stmt_execute($stmt);

        mysqli_stmt_store_result($stmt);        
        echo("Total Results:".mysqli_stmt_num_rows($stmt)."<br />");

        /* bind result variables */
        mysqli_stmt_bind_result($stmt, $user_name);

        /* fetch value */
        mysqli_stmt_fetch($stmt);

        printf("%s is having name %s\n", $user_id, $user_name);

        /* close statement */
        mysqli_stmt_close($stmt);

The mysqli num_rows returns 0 from mysqli OOP style

• Don't use mysqli_real_escape_string() anymore, if you are preparing the sql statements, because the preparation process already implies escaping. So, by using prepared statements you are on the safe side regarding MySQL injection.
• num_rows is not a method, but a property: $count = $login->num_rows;.
• You should use exception handling in order to be able to catch eventual errors.
• Use intention-revealing, pronounceable names for variables, properties and methods. And don't be afraid to provide long ones. See Clean, high quality code guide. Examples: dbconnection.php instead of condb.php, $connection instead of $cn, $statement instead of $login.

Maybe these answers can be of help for you too.

• Answer 1 I recommend you to use option 1.
• Answer 2

Good luck!

<?php

require_once 'condb.php';

/*
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception). They are catched in the try-catch block.
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings. 
 * 
 * See:
 *      http://php.net/manual/en/class.mysqli-driver.php
 *      http://php.net/manual/en/mysqli-driver.report-mode.php
 *      http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

if (!isset($_POST['LOGIN'])) {
    header('Location: index.php');
    exit();
} else {
    try {
        $username = $_POST['username'];
        $password = $_POST['password'];

        $sql = 'SELECT 
                name,
                username,
                password,
                status 
            FROM login 
            WHERE username = ?';

        $statement = $connection->prepare($sql);
        $statement->bind_param('s', $username);
        $statement->execute();
        $statement->store_result();

        $count = $statement->num_rows;

        if ($count > 0) {
            $varsBound = $statement->bind_result($resultName, $resultUsername, $resultPassword, $resultStatus);

            $fetched = $statement->fetch();

            // For testing.
            var_dump($resultName);
            var_dump($resultUsername);
            var_dump($resultPassword);
            var_dump($resultStatus);

            if (password_verify($resultPassword, password_hash($password, PASSWORD_DEFAULT))) {
                switch ($resultStatus) {
                    case 'Admin':
                        echo 'You are an Admin!';
                        // header("Location: admin/admin.php");
                        // exit();
                        break;

                    case 'Editor':
                        echo 'You are an Editor!';
                        //...
                        break;

                    case 'Author':
                        echo 'You are an Author!';
                        //...
                        break;

                    default:
                        //...
                        break;
                }
            } else {
                echo 'Invalid password!';
            }
        } else {
            echo 'Invalid user name or no record found for the given user name!';
        }

        $statement->free_result();
        $statement->close();
        $connection->close();
    } catch (mysqli_sql_exception $e) {
        echo 'Error: ' . $e->getCode() . ' - ' . $e->getMessage();
        exit();
    } catch (Exception $e) {
        echo $e->getMessage();
        exit();
    }
}

/*
 * Disable internal report functions.
 * 
 * MYSQLI_REPORT_OFF: Turns reporting off.
 * 
 * See:
 *      http://php.net/manual/en/class.mysqli-driver.php
 *      http://php.net/manual/en/mysqli-driver.report-mode.php
 *      http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;

---

Related Topics