Check if username exists not working

Question

So I'm using this code to check if a username exists in a database.

  $sql = $db->query("SELECT FROM people (username, password, email) WHERE username=$username");


 if(mysql_num_rows($sql)>=1)
   {
    echo"name already exists";
   }
 else
    {
    $sqll = $db->query("INSERT INTO people (username, password, created, ip) VALUES ('{$username}','{$ph}',NOW(),'{$ip}' )") or die("Error creating user");

    }

But when I run it I get

( ! ) Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /Users/idrisk/Desktop/localhost/r.php on line 17

I'm not really sure what I should do. Is there a better way to check if the username exists?

[Read this](http://stackoverflow.com/a/60496/2864740) and make changes before continuing too far. (Also, *why* are you mixing `$db->*` and `mysql_*`? This seems questionable at best. And why are you calling the *result* "$sql"? It's not, the string contains the SQL.) — user2864740, May 01 '14 at 19:08
What is `$db`? (I didn't know mysql_ had an "OO" interface..) — user2864740, May 01 '14 at 19:23
My database connection... `$db = new mysqli('x','x','x','x')` @user2864740 — , May 01 '14 at 19:27
*msqli_* is not *msql_*. Anyway, since you are using *mysqli* (not *mysql_*!), fix the query to use placeholders as per the link my first comment. Then, to check for a single row, simply try to read the row - don't check the count (which requires materialization). That is, simply do: `if ($sql->fetch()) { /* found a row! */ }`. But really, you should call $sql something else like $stmt to make the intent more clear. — user2864740, May 01 '14 at 19:46

score 1 · Accepted Answer · answered May 01 '14 at 19:05

1

You are getting that warning because your mysql query syntax is wrong (it's expecting a mysqli_result object as response but recieves a boolean of false).

Change:

"SELECT FROM people (username, password, email) WHERE username=$username"

To:

"SELECT username FROM people WHERE username='$username'"

You don't need the select the password and email because you are just comparing the username. Selecting the password and email for no reason is just a waste on memory.

answered May 01 '14 at 19:05

Karl Viiburg

832
10
34

Works perfect, is this considered a safe way to do it? – May 01 '14 at 19:09
@user302975 ofcourse, but as always, before executing any user input in a query, be sure to sanitize it with `mysql_real_escape_string()` or if using mysqli, then `mysqli_real_escape_string()` or `$mysqli->real_escape_string()` for OOP way – Karl Viiburg May 01 '14 at 19:11
So then this wouldn't work. `if($db->num_rows($sql)>=1){echo"name already exists";die(); }` How would I do it? – May 01 '14 at 19:38
@user302975 The general idea is correct but currently your trying to get the rows from the `Mysqli` object, what you need to do is get the rows from the result object. So instead of `$db->num_rows($sql)` do `$sql->num_rows`. You can find out more info about `num_rows` at http://www.php.net/manual/en/mysqli-result.num-rows.php – Karl Viiburg May 02 '14 at 05:58

score 0 · Answer 2 · answered May 01 '14 at 19:04

0

change this

  $sql = $db->query("SELECT FROM people (username, password, email) WHERE username=$username");

to

  $sql = $db->query("SELECT username, password, email FROM people  WHERE username= '$username' ");

you have to select columns from table.

answered May 01 '14 at 19:04

echo_Me

37,078
5
58
78

Check if username exists not working

2 Answers2