遇到问题mysqli_stmt（程序风格）

Question

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:

• how to make 2 queries with mysqli_stmt

Answer 1

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.

• http://php.net/manual/en/mysqli.prepare.php

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:

• http://php.net/manual/en/mysqli.error.php
• http://php.net/manual/en/function.error-reporting.php

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:

• check if row exists with mysql

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.

• http://www.php.net/manual/en/mysqli-stmt.store-result.php

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.";

}

• You can revert back to the way you used the conditional, but remember to "store" the result.