PHP & MySQL username validation and storage problem.

Question

For some reason when a user enters a brand new username the error message <p>Username unavailable</p> is displayed and the name is not stored. I was wondering if some can help find the flaw in my code so I can fix this error? Thanks

Here is the PHP code.

if($_POST['username'] && trim($_POST['username'])!=='') {
    $u = "SELECT * 
          FROM users 
          WHERE username  = '$username'
          AND user_id <> '$user_id'";
    $r = mysqli_query ($mysqli, $u) or trigger_error("Query: $u\n<br />MySQL Error: " . mysqli_error($mysqli));

    if (mysqli_num_rows($r) == TRUE) {
        echo '<p>Username unavailable</p>';
        $_POST['username'] = NULL;
    } else if(isset($_POST['username']) && mysqli_num_rows($r) == 0 && strlen($_POST['username']) <= 255) { 
        $username = mysqli_real_escape_string($mysqli, $_POST['username']);
    } else if($_POST['username'] && strlen($_POST['username']) >= 256) {
        echo '<p>Username can not exceed 255 characters</p>';
    }
}

Answer 1

Hey, mysqli_num_rows will always be true since your query is valid. Instead, you have to check the number of rows that it has returned, which should be zero of course if you'd like to create a new user. Therefore, check if the number of rows equals to 1.

if (mysqli_num_rows($r) == 1) echo "<p>Username unavailable</p>"; $_POST['username'] = NULL;

Or do it this way:

if (mysql_i_num_rows($r) == 0 ) {
  // There isn't a user with this username yet, so create new user
} else {
  echo "Username not available";
}

Answer 2

Anyway, it's a bad practice compare it with TRUE because it always return an int, use instead:

mysqli_num_rows($r) > 0

Answer 3

since you have not specifie dthe values of $username and $user_id your code of

$u = "SELECT * 
          FROM users 
          WHERE username  = '$username'
          AND user_id <> '$user_id'";

will evaluate to

$u = "SELECT * 
              FROM users 
              WHERE username  = ''
              AND user_id <> ''";

i would guess that this was not your inteded querry, and you will be retrievinga ll entries with a blank user name.