• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 71
  • Last Modified:

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>");
            }
                  }

}


?>
0
Aimee Katherine
Asked:
Aimee Katherine
  • 5
  • 4
1 Solution
 
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
1
 
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.)
1
 
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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.
0
 
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.
1
 
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

1
 
Aimee KatherineAuthor Commented:
Thanks.  It works the the not null.  I was just wondering if it could also work the other way.  =)
0
 
Aimee KatherineAuthor Commented:
Thank you so much!  I so greatly appreciate it!!
0
 
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.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now