MySQLi - Always Returning 1 Row

Question

So, I have written a function which checks my database to see if the username is already taken.

Basically, it is always returning 1 for the amount of rows, even if the variable $user is not a username in the database.

Not sure why, any ideas?

Function:

function userTaken($user){
    require_once('db.php');
    $taken = mysqli_query($mysqli, "SELECT * FROM users WHERE username = '$user'");
    if (!$taken) {
        die(mysqli_error($mysqli));
    }
    $cnt = mysqli_num_rows($taken);
    if($cnt > 0){
        return true;
    } else {
        return false;
    }
}

Calling Function:

if(userTaken($user)){
    echo "<font color='red'>That username is already taken!</font>";
}

The variable `$mysqli` is not in scope inside the `userTaken()` function. You need to pass it as a parameter `userTaken($user, $mysqli)` or access it with a `global` keyword. I would guess that you are converting this code from `mysql_query()`, or are previously familiar with `mysql_query()`. MySQLi is not a direct drop-in replacement, unfortunately. — Michael Berkowski, Jan 04 '15 at 03:04
Well then, if `mysqli_num_rows()` is returning 1 no matter the input, the rows in your table must not be what you expect them to be. You aren't escaping `$user` against injection in this function by the way. Now is the time to begin learning to use [`prepare()/bind_param()/execute()`](http://php.net/manual/en/mysqli.prepare.php) in MySQLi. — Michael Berkowski, Jan 04 '15 at 03:09
Hmm, I have dabbled around learning about preparing, binding parameters and stuff to make things more secure. I have read a bit about PDO and stuff but I am too old-school to understand. Now may be a good time to start learning for me. Would you be able to convert my function and make it more secure using preparation and stuff? Then I can mark your answer as correct if it fixes it :) @MichaelBerkowski — user3807836, Jan 04 '15 at 03:12
That's sort of a different issue entirely from this question. To debug this, you should first hard-code the value of `$user` into the query. If the `mysqli_num_rows($taken)` returns > 0, go ahead and `mysqli_fetch_assoc($taken)` the rows to dump them out and see what you're actually getting back. I would bet it's a data issue. I do recommend PDO over MySQLi because its prepared statement API is easier to use. [This tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) place PDO in context of the old `mysql_query()` and is quite good. Focus on the `prepare()/execute()` section.. — Michael Berkowski, Jan 04 '15 at 03:36
When I do that, it returns this: array(1) { ["username"]=> string(5) "lunar" } @MichaelBerkowski — user3807836, Jan 04 '15 at 03:48
And if I make $user a username that is not in the database, it will still return the exact same thing: array(1) { ["username"]=> string(5) "lunar" } — user3807836, Jan 04 '15 at 03:49
I have re-opened the question because I can't find any reason why this is a duplicate of the marked question. — Niet the Dark Absol, Jan 14 '15 at 16:36
Try to do `echo "SELECT * FROM users WHERE username = '$user'";`. To see *exactly* what query you are running. — gen_Eric, Jan 14 '15 at 16:38
How would that work @RocketHazmat, it will just literally echo exactly what is put in the quotation marks. — user3807836, Jan 14 '15 at 16:39
@user3807836: Yes. Then you can see what query is *actually* being ran. (Notice that I am using *double quotes* which will interpolate the value of `$user`. Also notice that in your code you are using this *exact same* string.) — gen_Eric, Jan 14 '15 at 16:41

score 0 · Answer 1 · answered Jan 14 '15 at 16:54

0

Try to check first and then query

 if(isset($user) && $user!='') 
 {
  $taken = mysqli_query($mysqli, "SELECT * FROM users WHERE username = '$user'");
 }

answered Jan 14 '15 at 16:54

A l w a y s S u n n y

36,497
8
60
103

score 0 · Accepted Answer · answered Jan 14 '15 at 17:02

It has been my experience that MySQL can return some funky values for row_count

maybe

 result = $db->query($mysqli, "SELECT COUNT(*) AS cnt FROM users WHERE username = '$user'")
 while($row = $result->fetch_assoc()){
    echo $row['username'] . '<br />';
}

or try

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
}

http://php.net/manual/en/mysqli.query.php

MySQLi - Always Returning 1 Row

2 Answers2