So, I am currently trying to make a login form for my browser game, which requires multiple queries to work properly. I first started with the normal procedure of querying using PHP and MySQL but soon discovered it wasn't the best way to do it because of SQL injection.
So, I decided to use the stmt, which according to stackoverflow, is safer.
My code is bigger than this, but I will just put here the part that is bugging (I have debugged the rest of the code and everything else is fine, including connection to the MySQL server)
$stmt = mysqli_prepare($conn, "SELECT username FROM users WHERE username='$playername'");
´
//Im pretty sure this is where the bug is
mysqli_stmt_bind_param($stmt, "s", $playername);
//----------------------------------------
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $dbusername);
mysqli_stmt_fetch($stmt);
$row_cnt = mysqli_stmt_num_rows($stmt);
if($row_cnt === 0) {
mysqli_stmt_close($stmt);
$error = true;
$errorid = "There is no player registered with that username.";
echo $errorid;
}
I have created an entry in the database with the username "Syvered" which is the one i am testing at the moment, and when trying to use that username on the login form (notice that $playername is the inputed username by the user) it still says "There is no such user with that username" which means that mysqli_stmt_num_rows($stmt) is returning 0 for some reason. This is what I dont understand.
I really hope I have been clear enough to you, thank you in advance for your help.
Questions I checked but unfortunately didn't help:
You're passing a variable in the WHERE clause:
WHERE username='$playername'
instead of a placeholder, which needs to be changed to:
WHERE username=?
since you're wanting to use a prepared statement.
Make sure that $playername does have a value and that you've successfully connected using the mysqli_ API.
Using proper error checking would have helped:
If you're looking to see if a row exists (which seems to be the case here), see one of my answers which uses a prepared statement:
and a PDO method also.
An example taken from one of my answers, which is what you need to do and replace it with what you're using in the query and variable(s):
$query = "SELECT `email` FROM `tblUser` WHERE email=?";
if ($stmt = $dbl->prepare($query)){
$stmt->bind_param("s", $email);
if($stmt->execute()){
$stmt->store_result();
$email_check= "";
$stmt->bind_result($email_check);
$stmt->fetch();
if ($stmt->num_rows == 1){
echo "That Email already exists.";
exit;
}
}
}
Edit:
After testing your code, there is something you are not doing correctly here.
You need to "store" the results which was missing in your code.
Yet, let's try a slightly different approach and check if it does exist and echo that it does, and if not; show that it doesn't.
Sidenote: I used >= in if($row_cnt >= 1) should there be more than one matching. You can change it if you want.
$playername = "Syvered"; // This could also be case-sensitive.
$stmt = mysqli_prepare($conn, "SELECT username FROM users WHERE username = ?");
mysqli_stmt_bind_param($stmt, "s", $playername);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt); // Store the results which was missing.
mysqli_stmt_bind_result($stmt, $dbusername);
mysqli_stmt_fetch($stmt);
$row_cnt = mysqli_stmt_num_rows($stmt);
if($row_cnt >= 1) {
$error = false; // Changed from true
$errorid = "It exists.";
echo $errorid;
mysqli_stmt_close($stmt);
}
else{
echo "It does not exist.";
}