My mysqli statement is not storing any data

As a part of my training, my team leader asked me to make a simple login in PHP, I have created a controller that receives the username and the password from a JavaScript file, and then triggers an SQL query using parameters, that’s where my first problem starts. I execute the statement but then I can’t manage to store the query dataset in my $stored variable. I’m using store_result(), and neither have any rows being returned ($queryRows variable).

What am I doing wrong?

<?php
include('database.php');
session_start();

//Fetch the user data
$user = $_POST['user'];
$password = $_POST['pass'];
$_SESSION['user'] = $user;


//Checks for empty values 
if(empty($user)||empty($password)){
    header("Location: ../views/login.php?error=emptyfields");
    exit();
}

//DB query to validate the user/password combination
$validationQuery = "SELECT userName, password FROM users WHERE userName =? AND password =?";

//Prevents SQLInjection by using parameters

$statement = $connection->prepare($validationQuery);
$statement->bind_param("ss",$user,$password);
$executed = $statement->execute();
$stored = $statement->store_result($statement);
$queryRows = $statement->num_rows($statement);


if ($stored) {
    header("Location: ../views/home.php");
    exit();
} else {
    header("Location: ../views/login.php?error=userError");
    exit();
}
mysqli_stmt_close($statement);
mysqli_close($connection);

Answer

You are doing really well by using prepared statements. However, mysqli is a very complicated extension. I highly recommend looking up each function in the documentation carefully before using it.

In your case, your code looks like it makes sense, but when you look closer it’s pure nonsense.

The first 3 lines work fine. It prepares and executes a statement on the MySQL server.

$statement = $connection->prepare($validationQuery);
$statement->bind_param("ss",$user,$password);
$executed = $statement->execute();

mysqli_stmt::store_result() is used to store the data in the internal buffer. It returns true when the data was buffered properly. It doesn’t take any arguments!

$stored = $statement->store_result($statement);

mysqli_stmt::num_rows() returns the number of rows buffered. It doesn’t take any arguments!

$queryRows = $statement->num_rows($statement);

After this code will be executed, $stored will always be true. Even if the result set has 0 rows. This means that the next statement will be executed.

if ($stored) {
    header("Location: ../views/home.php");
    exit();
}

Here, your script ends. You redirect to another page and exit the current one. The internal buffer is cleared, the statement is cleaned up, and the connection is closed.

The following two lines are no-op code:

mysqli_stmt_close($statement);
mysqli_close($connection);

I am not sure what exactly your intentions were, but given that this is a login script, I would say that you want to retrieve the data from the statement, not the number of rows. I would remove store_result() and use get_result() instead.

$statement = $connection->prepare($validationQuery);
$statement->bind_param("ss",$user,$password);
$executed = $statement->execute();

$result = $statement->get_result();
$userRecord = $result->fetch_assoc();
if($userRecord && password_verify($password, $userRecord['hash']) {
    $_SESSION['userId'] = $userRecord['id'];

    header("Location: ../views/home.php");
    exit();
} else {
    header("Location: ../views/login.php?error=userError");
    exit();
}
// NO MORE CODE SHOULD GO HERE. NO PHP, no HTML, nothing. This is the end of the script!

My example of course uses password hashing like every login script should. You need to adjust your SQL statement to account for that.