I am trying to create a login page, but I'm having some issues using prepared statements to secure the login. I have the following code:
$sql = "SELECT * FROM users WHERE user_email=?";
$stmt = mysqli_stmt_prepare($db, $sql);
mysqli_stmt_bind_param($stmt, "s", $email);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$resultCheck = mysqli_stmt_num_rows($stmt);
The problem occurs when checking if the result check variable is less than 1. It shouldn't be 0, but it is. I don't understand why, as the database has an email with the value test@test.com, but when trying to enter that the $resultCheck variable still returns 0. I'm guessing it has to do with the prepared statements.
The client has no idea how many rows are in the result until they are fetched.
You can make the client pre-fetch all rows of the result by using mysqli_stmt_store_result(). Then you can use num-rows.
$sql = "SELECT * FROM users WHERE user_email=?";
$stmt = mysqli_prepare($db, $sql);
mysqli_stmt_bind_param($stmt, "s", $email);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$resultCheck = mysqli_stmt_num_rows($stmt);
echo "result num_rows = $resultCheck
";
This echo correctly produces the answer "1".
But if you do use store-result, for some reason you can't also use get-result. So you can't use result methods like fetch_assoc — you have to bind_result into variables by reference and use fetch().
By the way, mysqli_stmt_prepare() takes a statement object as its first argument, not the $db connection. Whereas mysqli_prepare() takes a connection object. Again, a confusing usage of mysqli functions.
I don't like mysqli. It's hard to use and has confusing traps of inexplicable behavior. I don't like how bind_param and bind_result make my code seem cluttered.
I prefer using PDO. It's much easier.
$sql = "SELECT * FROM users WHERE user_email=?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$email]);
$result = $stmt->fetchAll();
$rowCount = $stmt->rowCount();