Switching to prepared statements...mysqli_stmt_num_rows($stmt);

Hi! I'm trying to change all of my SQL queries to prepared statements.  I can't figure out why my prepared statement below is not working.  It's a login screen.  I get no errors with the prepared statement, but it doesn't redirect the user to index.php.  That is the problem that I have.  That it is not redirecting. The problem seems to be with mysqli_stmt_num_rows($stmt); , because when I take that condition away, the code works.  I'm not sure what's wrong with that condition.  Can anyone help me please?  Any help is greatly appreciated.  

Old code that works:
<?php

if (isset($_POST['login_button'])) {

$email = filter_var($_POST['log_email'], FILTER_SANITIZE_EMAIL); //removes illegal characters from the email
$_SESSION['log_email'] = $email; //store email into session variable

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

$check_database_query = mysqli_query($conn, "SELECT * FROM users WHERE email='$email' AND password = '$password' ");

$check_login_query = mysqli_num_rows($check_database_query);

      if($check_login_query == 1){
            
      $row = mysqli_fetch_array($check_database_query);  
      $id = $row['id'];  
      $username = $row['username'];

      $_SESSION['id'] = $id;  
      $_SESSION['username'] = $username;

      header("Location: index.php"); //send the user back to index.php, main page after they've logged in

      exit();  
      } else {array_push($errorlog_array, "E-mail or password was incorrect<br>");
            }

}

?>




New code that doesn't work:

<?php

if (isset($_POST['login_button'])) {

$email = filter_var($_POST['log_email'], FILTER_SANITIZE_EMAIL); //removes illegal characters from the email
$_SESSION['log_email'] = $email; //store email into session variable

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

$check_database_query = "SELECT * FROM users WHERE email= ? AND password = ? ";
$stmt = mysqli_stmt_init($conn);
      if(!mysqli_stmt_prepare($stmt, $check_database_query)){
                        echo "SQL error";
                  } else {
                        mysqli_stmt_bind_param($stmt, "ss", $email, $password);
                        mysqli_stmt_execute($stmt);
                        $result = mysqli_stmt_get_result($stmt);
                        $check_login_query = mysqli_stmt_num_rows($stmt);
                        if($check_login_query == 1){            
                              $row = mysqli_fetch_assoc($result);           
                             $id = $row['id'];  
                               $username = $row['username'];
                              $_SESSION['id'] = $id;  
                              $_SESSION['username'] = $username;
                              header("Location: index.php");

      exit();  
      } else {array_push($errorlog_array, "E-mail or password was incorrect<br>");
            }
                  }

}


?>
Aimee KatherineAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan LouwerensSoftware EngineerCommented:
It looks like you might need to call mysqli_stmt_store_result before calling mysqli_stmt_num_rows.

Reference: http://php.net/manual/en/mysqli-stmt.num-rows.php
Jan LouwerensSoftware EngineerCommented:
Another option would be to just check $row variable for null after the call to $row = mysqli_fetch_assoc($result);. ($row will be null if there were no rows returned.)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aimee KatherineAuthor Commented:
Hi thank you so much for your response!   Yes, when I add mysqli_stmt_store_result, it checks     if($check_login_query == 1){     and tries to execute within that if statement.  But I noticed that when I try to use mysqli_stmt_store_result, then mysqli_stmt_get_result doesn't work, and the variables aren't assigned.  If I leave out mysqli_stmt_store_result (and hence mysqli_stmt_num_rows($stmt); ), then the variables are assigned, it can log in, but then I will be performing the operation without checking num_rows.  They both work, but without each other (in this query).  I don't know why.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Aimee KatherineAuthor Commented:
Jan Louwerens,
Your tip about using not null is great and works.  =)  Thanks!  I'm just curious if it's possible to use both mysqli_stmt_store_result and mysqli_stmt_get_result to fix the above issue as well.
Jan LouwerensSoftware EngineerCommented:
I think if you use the checking for null method, you wouldn't need to call mysqli_stmt_store_result at all (since you also wouldn't be calling mysqli_stmt_num_rows).

The checking for null is meant to be used in place of the mysqli_stmt_num_rows call.
Jan LouwerensSoftware EngineerCommented:
This is how I imagine your new code working:

<?php
   if (isset($_POST['login_button']))
   {
      $email = filter_var($_POST['log_email'], FILTER_SANITIZE_EMAIL); //removes illegal characters from the email
      $_SESSION['log_email'] = $email; //store email into session variable

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

      $check_database_query = "SELECT * FROM users WHERE email= ? AND password = ? ";
      $stmt = mysqli_stmt_init($conn);

      if (mysqli_stmt_prepare($stmt, $check_database_query))
      {
         mysqli_stmt_bind_param($stmt, "ss", $email, $password);
         mysqli_stmt_execute($stmt);

         $result = mysqli_stmt_get_result($stmt);
         $row = mysqli_fetch_assoc($result);
         if ($row != null)
         {
            $id = $row['id'];
            $username = $row['username'];

            $_SESSION['id'] = $id;
            $_SESSION['username'] = $username;

            header("Location: index.php");

            exit();
         }
         else
         {
            array_push($errorlog_array, "E-mail or password was incorrect<br>");
         }
      }
      else
      {
         echo "SQL error";
      }
   }
?>

Open in new window

Aimee KatherineAuthor Commented:
Thanks.  It works the the not null.  I was just wondering if it could also work the other way.  =)
Aimee KatherineAuthor Commented:
Thank you so much!  I so greatly appreciate it!!
Jan LouwerensSoftware EngineerCommented:
Yes, I do believe it could work the other way, too. (It might be redundant, but it should still be possible.)

I think you'd just need to call the functions in the proper order:
  1. mysqli_stmt_execute
  2. mysqli_stmt_store_result
  3. mysqli_stmt_num_rows
  4. mysqli_stmt_get_result

It looks like there's also a mysqli_stmt_free_result, which you might want to call to free up the memory allocated by mysqli_stmt_store_result, before closing the statement.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.