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

Making a login form and this is my first time using prepared statements. My issue is the num_rows keeps returning 0, despite entering the correct email and password that matches the email and password of my table. I tested that the connection works and the SQL statement works also, its just the num_rows is always 0.

PHP(without php tags and connection code):

    $email = $_POST['email'];
    $password = md5($_POST['password']);

    if(!($stmt = $con->prepare("SELECT `email`, `password` FROM users WHERE `email` = ? AND  `password` = ?")))
    {
       echo "Prepare failed: (" . $con->errno . ")" . $con->error;
    }
    else
    {
       echo " Query read \n";

       $stmt->bind_param('ss', $email, $password);
       $stmt->execute();
       $stmt->store_result();
       $num_of_rows = $stmt->num_rows;
       $stmt->bind_result($email, $password);

       echo $num_of_rows;

        if($num_of_rows == 1) //To check if the row exists
        {
            echo "Exists";

               if($stmt->fetch()) //fetching the contents of the row
               {
                  echo "Exists";
                  $_SESSION['loggedin'] = true;
                  $_SESSION['message'] = "logged in";
                  $_SESSION['email'] = $email;
                  echo "Success!";
                  exit();
               }
        }

        else 
        {
           echo "Error";
        }
     }

Hopefully I've just forgotten something, but either way I am stumped.

Thanks in advance!

3 answers

  • answered 2018-02-13 01:17 Nikos Takiris

    You also want to fetch the results, like this:

    $stmt->bind_param('ss', $email, $password);
           $stmt->execute();
           $stmt->store_result();
           $stmt->fetch();
           $num_of_rows = $stmt->num_rows;
           $stmt->bind_result($email, $password);
           echo $num_of_rows;
    

  • answered 2018-02-13 01:25 spencer7593

    The value returned by num_rows may not be a valid count of rows returned until all of the rows are retrieved. That's the case for a mysqli_result. The documentation makes it appear that the num_rows function of a mysqli_stmt should be available immediately after a store_result.

    Seems like the most reasonable explanation for the behavior is that the query did not return a row.

    Documentation:

    http://php.net/manual/en/mysqli-result.num-rows.php

    http://php.net/manual/en/mysqli-stmt.num-rows.php


    Why do we need to use num_rows at all? That just seems like a lot of unneeded clutter. We could just do the fetch. If it returns TRUE, we know there was at least one row returned. If it's FALSE, then zero rows were returned. No need to muck with num_rows.

    If we are going to use store_result, its a good pattern to follow that with a free_result once we're done with the resultset


    Also, do not use MD5 for password hash. And there's no need to return the password hash from the database, we can omit that from the SELECT list.

    https://security.stackexchange.com/questions/19906/is-md5-considered-insecure


  • answered 2018-02-13 02:26 Anton De Rose

    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;