1

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:

Community
  • 1
  • 1
Syvered
  • 43
  • 7

1 Answers1

3

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

}
  • You can revert back to the way you used the conditional, but remember to "store" the result.
Community
  • 1
  • 1
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
  • Thank you for your answer! Unfortunately I did what you told me to and it still isn't working. I have edited my question's code. – Syvered Dec 27 '16 at 17:33
  • @Syvered as I said in my answer, see another of my answers http://stackoverflow.com/questions/22252904/check-if-row-exists-with-mysql/22253579#22253579 which work and basically does the same thing. If yours didn't work, then something is failing you and you need to check for errors as to why it did fail. – Funk Forty Niner Dec 27 '16 at 17:34
  • @Syvered I had to do a rollback to your original post, since you should have made it as an additional edit. I would have been downvoted for it, where people would have said to themselves: *"they're using a placeholder, so why the answer?"*. – Funk Forty Niner Dec 27 '16 at 17:35
  • Oh, sorry! I am new to this stackoverflow thing. I did check that link but there isnt a procedural way of doing it in it is there? – Syvered Dec 27 '16 at 17:36
  • @Syvered both procedural and object oriented work, why the need for procedural? You know that those can work together, it's not recommended but it does work. Again, check for errors via PHP and the query and I have links in my answer for you to consult that will help you debug. – Funk Forty Niner Dec 27 '16 at 17:38
  • Thank you very much! I am currently checking for error using the links you provided, hopefully I will find whats wrong with it :) – Syvered Dec 27 '16 at 17:42
  • @Syvered You're welcome. I made a slight edit to my answer. That is what you need to use here, is to excute, store the result, bind then fetch and if `num_rows` returns true/1. – Funk Forty Niner Dec 27 '16 at 17:44
  • Guess I will be using it if I don't find a better solution. Thanks once again for your time – Syvered Dec 27 '16 at 17:47
  • @Syvered You're welcome. Reload my answer and look under **Edit:**, you needed to "store" the results. I tested that and it worked. See the comments inside my edited code. – Funk Forty Niner Dec 27 '16 at 18:32
  • Worked perfectly! Thanks once again, you're the best :D – Syvered Dec 27 '16 at 18:51