Why Does MySQLi Num_Rows Always Return 0

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 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() 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

Possible Bug: http://www.php.net/manual/en/mysqli-result.num-rows.php#104630

Code is from source above (Johan Abildskov):

$sql = "valid select statement that yields results"; 
if($result = mysqli-connection->query($sql, MYSQLI_USE_RESULT)) 
{ 
          echo $result->num_rows; //zero 
          while($row = $result->fetch_row()) 
        { 
          echo $result->num_rows; //incrementing by one each time 
        } 
          echo $result->num_rows; // Finally the total count 
}

Could also validate with the Procedural style:

/* determine number of rows result set */
$row_cnt = mysqli_num_rows($result);

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;

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

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;

num_rows not working pdo,mysqli, always return 0

in pdo, num_rows won't work.
you have to use $sql->rowCount() method to get number of records in a table.

<?php
    $sql = $con->prepare("<YOUR SQL QUERY HERE>");
    $sql->execute();
    if($sql->rowCount() > 0){
        echo $sql->rowCount() ." rows found";
    }
?>

MYSQLi num_rows always returns 0

Column names should not be enclosed with single or double quotes, instead you can use backticks (`)

SELECT `password`,`ban` FROM users WHERE `username`=? LIMIT 1

---

Related Topics