Check if username exists in MySQL table using PHP?

Question

I want to create a code to check if username already exists on server or not using MySQL and PHP. The code:

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT username FROM Servers where email=".$user_info['email']."";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
//username exists
}
else
{
 //...
}

I keep getting this error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in

may be your query has an error use it like -> "SELECT username FROM Servers where email=" '.$user_info['email'].' " "; — Ankur Tiwari, Sep 13 '15 at 08:11
You are vulnerable to [MySQL injection attacks](http://bobby-tables.com/php.html). Use data binding instead. — Anders, Sep 13 '15 at 08:12
You could also set unique indexes on the username column (and email if you like) and let the server tell you when you try to add a duplicate. (The only reason I could come up with to want to check "if username already got a server") — Ragdata, Sep 13 '15 at 08:21

score 1 · Answer 1 · answered Sep 13 '15 at 09:32

You don't want to generate SQL queries by concatenating user input to a string. This could result in SQL injection.

You want to parameterize your query. First you declare the structure of your query to MySQL then you bind your parameters and call it to get the result. It is like using a function: you rarely generate it on the fly using your user input. Think of your SQL queries as functions.

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$emailIsValid = false;
// Initialize a query. Note how we replaced the variable by a '?'. This indicates a parameter.
$sql = "SELECT username FROM Servers where email=?";
$query = mysqli_stmt_init($conn);
if (mysqli_stmt_prepare($query, $sql)){
    // Declare the user email as first parameter of the query
    mysqli_stmt_bind_param($query, "s", $user_info['email']);
    // Executes the query
    mysqli_stmt_execute($query);
    // Bind the result to a variable
    mysqli_stmt_bind_result($query, $result);
    // Read first result
    if(mysqli_stmt_fetch($query)){
        // At least one user, so the email exists
        $emailIsValid = true;
    }
    // If we don't need the query anymore, we remove it
    mysqli_stmt_close($query);
}

if ($emailIsValid) {
//username exists
}
else
{
 //...
}

Check the mysqli docs for more information. Although I would advise you to use PDO instead.

score 0 · Answer 2 · answered Sep 13 '15 at 08:27

0

Maybe this can solve the problem. modify the code a little..

$email = $user_info['email'];
$sql = "SELECT username FROM Servers where email='$email'";

answered Sep 13 '15 at 08:27

Shawon Kanji

668
4
12

score 0 · Accepted Answer · answered Sep 13 '15 at 08:45

0

Use this-

$sql = "SELECT username FROM Servers where email='".$user_info['email']."'";

answered Sep 13 '15 at 08:45

ashish singh

150
5

A1Gard · Answer 4 · 2015-09-13T08:21:24.920

This not correct way you must find count:

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT COUNT(username) AS 'count' FROM Servers where email= '".$user_info['email']."'";
$result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
$num = mysqli_fetch_array($result);
if ($num['count'] > 0) {
// user dublicate
} else{
// new user 
}

you must use quote ' for string in database
you must use count for optimize your code
you must bypass sql injection in your code.

and your code have sql injection bug.

Check if username exists in MySQL table using PHP?

4 Answers4