phpmysqlprepared-statementmysql-num-rows

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!


Solution

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