The mysqli num_rows returns 0 from mysqli OOP style

Question

I am working on a login script and using mysqli OOP style. I write the echo script to check that it fetched the data or not. The result is the login data. But when I want to check the num_rows, it's return zero even if I use $stmt->store_result. I searched for many threads on this website and it doesn't work. mysqli_num_rows doesn't work too. Or I just missed some order of data storing?

Here is my code.

<?php
require_once "condb.php";

if (!isset($_POST['LOGIN']))
  {header("Location:index.php");}

else
{

  $username=mysqli_real_escape_string($cn,$_POST['username']);
  $password=mysqli_real_escape_string($cn,$_POST['password']);
  $hashed_password=password_hash($password,PASSWORD_DEFAULT);

  $login = $cn->prepare("SELECT name,username,password,status
                       FROM login WHERE username=?");

  $login->bind_param("s", $username);
  $login->execute();
  $login->bind_result($name,$username,$password,$status);
  $login->store_result();
  $login->fetch();
  $count=$login->num_rows();

  echo $name."<br />".
  $username."<br />".
  $password."<br />".
  $status."<br />".$count;

/*Below here is if condition that I'll used when I finished solving this 
problem.*/

/*
if ($count > 0 )
{
   if(password_verify($password, $row['password']) && 
   $row['status']=="Admin")
   {
   echo "ok<br>You are Admin!";
   header("Location:admin/admin.php");
   $login->close();
   }elseif(password_verify($password, $row['password']) && 
    $row['status']=="Editor")
    {
      echo "ok<br>You are Editor!";
      $login->close();
    }elseif(password_verify($password, $row['password']) && 
    $row['status']=="Author")
    {
      echo "ok<br>You are Author!";
      $login->close();
     }
 else
 {
   echo "Username or password incorrect";
   $login->close();
   }
}
*/
}
?>

Answer 1

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

Answer 2

The answer to your question is simple. If (given that store_result() is called), num_rows returns 0, it means that your query found no rows. It's really simple. No need to look for some other code or blame a database. No rows to fetch? Then there is no such data in the database. Therefore, to make your query return something you need to make sure that $_POST['username'] contains a value that exists in the database.

Moreover, I must tell you that this function/variable is the most useless part of mysqli (or any other database API). Any time you need it and it's available, it can be substituted with something else that you already have. In your case you can check the result of fetch() instead, which will serve for this purpose perfectly.

Having said that, I have a feeling that even with a query everything is all right and your problem is from variables you are using. You are using a non-existent $row variable which obviously will return nothing. Have you swithched the proper error reporting in PHP, it would have told you that already. So in any case just remember to handle errors properly, you can refer to my article, PHP error reporting basics for that.

So, in the end your code should be like this:

<?php

if (!isset($_POST['LOGIN'])){
    header("Location:index.php");
    exit;
}
require_once "condb.php";

$sql = "SELECT name,username,password,status FROM login WHERE username=?";
$stmt = $cn->prepare($sql);
$stmt->bind_param("s", $_POST['username']);
$stmt->bind_result($name,$username,$password,$status);
$stmt->execute();

if ($login->fetch() and password_verify($_POST['password'], $password) {
    if ($status=="Admin")
    {
        header("Location:admin/admin.php");
        exit();
    }
    echo "ok<br>You are $status!";
} else {
   echo "Username or password incorrect";
}

As you can see, there were some other useless functions used in your code which I removed as well. You see, PHP code could be compact and tidy if you make it to do only what it have to do.

Answer 3

I wrote something that applied from your answers like this below.

<?php
error_reporting(E_ALL);
require_once "condb.php";

if (!isset($_POST['LOGIN']))
{header("Location:index.php");}

else
{

  $username=$_POST['username'];
  $receivedpassword=$_POST['password'];
  $hashed_password=password_hash($receivedpassword,PASSWORD_DEFAULT);

  $login = $cn->prepare("SELECT name,username,password,status
                         FROM login 
                         WHERE username=?");

  $login->bind_param("s", $username);
  $login->execute();
  $login->store_result();
  $login->bind_result($name,$getusername,$getpassword,$status);
  $login->fetch();
  $count=$login->num_rows;

  echo $name."<br />".$getusername."<br />".$getpassword."<br />".
  $status."<br />".$count."<br />";
  var_dump($name);echo "<br />";
  var_dump($getusername);echo "<br />";
  var_dump($getpassword);echo "<br />";
  var_dump($status);echo "<br />";
  var_dump($count);echo "<br />";
  // start checking a row

  if($count > 0)
  { // start of username check

    echo 'You are going good of your username.'."<br />";

    if(password_verify($receivedpassword, $getpassword))
    { // start of pw check

      echo 'Your password is going good!'."<br />";

      //start to check permission
      switch ($status)
      {// start of switch
        case 'Admin':
          echo 'You are admin.';
          break;
        case 'Editor':
          echo 'You are editor.';
          break;
        case 'Author':
         echo 'You are author.';
         break;
        case 'User':
          echo 'You are user.';
          break;
        default:
          echo 'NO PERMISSION FOUND.';
          break;
      }// end of switch

    } // end of pw check

    // else from pw verify
    else
    { // check of incorrect password
      echo 'Your password isn\'t going good.';
    } // end of incorrect password check

  } // end of username check

  // else from $count > 0
  else
  {// No username found condition check

    echo 'Your username isn\'t going good.';

  } // end of no username found

}//end of everything

?>