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>';
    }
}

Can you please elaborate on what you're trying to achieve? Not code-wise but the purpose. — VolkerK
– VolkerK, Commented May 2, 2010 at 9:21

Kris Van den Bergh · Accepted Answer · 2010-05-02 09:20:44Z

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

edited May 2, 2010 at 9:20

answered May 2, 2010 at 8:53

Kris Van den Bergh

1,1524 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chaim Over a year ago

I am not sure about that. In PHP false, 0 and null are all equal to each other. So if the value is checked as true than it should be greater than 0. In other words: if($val == false); if($val == 0); if($val == ""); and if($val == null); Are all the same. If the string contains any value (other than false/zero/null) than it is true.

Cesar · Accepted Answer · 2010-05-02 09:08:46Z

0

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

mysqli_num_rows($r) > 0

answered May 2, 2010 at 9:08

Cesar

4,4372 gold badges33 silver badges37 bronze badges

Comments

marc_s · Accepted Answer · 2013-02-11 21:57:10Z

0

Since you have not specified the 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 intended query, and you will be retrieving all entries with a blank user name.

edited Feb 11, 2013 at 21:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 2, 2010 at 9:09

Bingy

6444 silver badges9 bronze badges

Collectives™ on Stack Overflow

PHP & MySQL username validation and storage problem

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related