PHP MYSQLI prepared statements login and check the user status

Question 1

I am learning to make website with some video tutorials based on mysqli. I came to know that using prepared statements are more secure and I am trying to create a login system. Here is what I have done so far.

This code helps me login success fully.

<form action ="" method="post">

User Name:<br/>
<input type='text' name='username' />
<br/><br/>
Password:<br/>
<input type='password' name='password' />
<br/><br/>
<input type='submit' name='submit' value='login'>
</form>
<?php

if(isset($_POST['submit'])){
    $username = $_POST['username'];
    $password = md5($_POST['password']);
    $stmt = $con->prepare("SELECT username, password FROM users WHERE username=? AND  password=? LIMIT 1");
    $stmt->bind_param('ss', $username, $password);
    $stmt->execute();
    $stmt->bind_result($username, $password);
    $stmt->store_result();
    if($stmt->num_rows == 1)  //To check if the row exists
        {
            while($stmt->fetch()) //fetching the contents of the row

              {$_SESSION['Logged'] = 1;
               $_SESSION['username'] = $username;
               echo 'Success!';
               exit();
               }
        }
        else {
            echo "INVALID USERNAME/PASSWORD Combination!";
        }
        $stmt->close();
    }
    else 
    {   

    }
    $con->close();
?>

But I also need to check if the user have not activated or have been banned or deactivated. So I made another code.

And here is the code I made

<?php
if(isset($_POST['submit'])){
$username = $_POST['username'];
$password = md5($_POST['password']);
$stmt = $con->prepare("SELECT username, password FROM users WHERE username=? AND    password=? LIMIT 1");
$stmt->bind_param('ss', $username, $password);
$stmt->execute();
$stmt->bind_result($username, $password);
$stmt->store_result();
if($stmt->num_rows == 1)  //To check if the row exists
  {
 $result=$con->query($stmt);
            $row=$result->fetch_array(MYSQLI_ASSOC);
            $user_id= $row['user_id'];
            $status = $row['status'];
            if($status=='d'){
                echo "YOUR account has been DEACTIVATED.";
            }else{
                $_SESSION['Logged'] = 1;
                $_SESSION['user_id'] = $user_id;
                $_SESSION['username'] = $username;
                echo 'Success!';
               exit();
            }
        }
        else {
            echo "INVALID USERNAME/PASSWORD Combination!";
        }
        $stmt->free_result();
        $stmt->close();
        
    }
    else 
    {   

    }
 $con->close();
 ?>

When I use this I get the following errors

Warning: mysqli::query() expects parameter 1 to be string, object given in F:XAMPPhtdocsloginlogin.php on line 33

Fatal error: Call to a member function fetch_array() on a non-object in F:XAMPPhtdocsloginlogin.php on line 34

I have database table columns

user_id, username, password (md5), user_level, status.

Under user_level I have the following

a = admin
m = member

Under status

a = activated
n = not activated
d = deactivated
b = banned

While logging in I need to check if the user status and if it is activated it should move to index page or if it is d it should show the user has been deactivated and likewise for others.

How to do it in prepared statements?

And I have this connect.php in all page

?php
//error_reporting(0);
'session_start';
$con = new mysqli('localhost', 'username', 'password', 'database');
if($con->connect_errno > 0){
die('Sorry, We're experiencing some connection problems.');
}
?>

Question 2

I think you need to take a look into how mysqli_ works. This should get you in the right direction.

if(isset($_POST['submit'])){
    $username = $_POST['username'];
    $password = md5($_POST['password']);
    $user_id = 0;
    $status = ""

    $stmt = $con->prepare("SELECT user_id, username, password, status FROM users WHERE username=? AND password=? LIMIT 1");
    $stmt->bind_param('ss', $username, $password);
    $stmt->execute();
    $stmt->bind_result($user_id, $username, $password, $status);
    $stmt->store_result();
    if($stmt->num_rows == 1)  //To check if the row exists
        {
            if($stmt->fetch()) //fetching the contents of the row
            {
               if ($status == 'd') {
                   echo "YOUR account has been DEACTIVATED.";
                   exit();
               } else {
                   $_SESSION['Logged'] = 1;
                   $_SESSION['user_id'] = $user_id;
                   $_SESSION['username'] = $username;
                   echo 'Success!';
                   exit();
               }
           }

    }
    else {
        echo "INVALID USERNAME/PASSWORD Combination!";
    }
    $stmt->close();
}
else 
{   

}
$con->close();

Question 3

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Question 4

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Question 5

Yes, there is.

Some time ago an invaluable feature has been added to PHP - an argument unpacking operator. It has a billion uses, and helping you in this situation is among them.

Just add ...[ before your list of values and ] after - and voila, it works!

$stmt->bind_param('ssssssisssssssssi', ...[
  $my_class->get(FIELD_ONE),
  $my_class->get(FIELD_TWO),
  /*...x15 more...*/
  ]);

A hint: this useful operator could be also used to encapsulate that boring prepare/bind/execute process in a simple function.

Question 6

Presuming you're doing something like:

$result = $statement->execute();

You can get the number of rows with

$result->num_rows;

See the manual.

PHP MYSQLI prepared statements login and check the user status

Answers

php,mysqli