Mysql – Checking if an Username is already in use

MySQLPHP

I am writing some scripts for my website, and I am currently focussing on checking if a username is already in use. I have tried writing a SELECT script where the datafield is selected and checks if the POSTed data matches.

Here is my code:

$username = $_POST["username"];
$password = $_POST["password"];

{ // Check if account already exists

        $Select_Account = "SELECT ";
        $Select_Account .= "Username ";
        $Select_Account .= "FROM UserData ";
        $Select_Account .= "WHERE UserData.Username ";
        $Select_Account .= "= '$username'";

        $Select_Account_Query = mysql_query($Select_Account);

        if ($Select_Account_Query) {
            echo "Account Exists";
        } else {
            echo "Account does not exist";
        }

    }

I cannot figure out the issue that is preventing the code from ECHOing "Account does not exist" when a value that it inputted is not inside of the table.

Please be aware that I am quite new to writing PHP and MySQL, so I am not fully up to scratch with all the queries and the syntax yet. All help will be greatly appreciated.

Best Answer

mysql_query() returns false only when there is something wrong with your query and it fails to execute. Otherwise, the return value is a resource which evaluates to success. That is your code always echoes "Account Exists". Check for the number of rows the SELECT statement fetched to determine whether the user exists or not.

Your code is also not secure. It is vulnerable to SQL injection. I would highly recommend NOT using mysql_* family of PHP functions. They are deprecated as of PHP 5.5.0 and will be removed in future releases. Consider switching over to mysqli_* family of functions or better use parameterized queries with PDO.

PDO:

$conn = new PDO('mysql:host='.$dbhost.';dbname='.$dbname,
                 $dbusername,
                 $dbpassword);

$username = $_POST["username"];
$password = $_POST["password"];


//Query statement with placeholder
$query = "SELECT Username 
          FROM Userdata 
          WHERE Userdata.Username = ?";

//Put the parameters in an array
$params = array($username);

//Execute it
try {
    $stmt = $conn->prepare($query);
    $result = $stmt->execute($params);
} catch(PDOException $ex) {
    echo $ex->getMessage());
}

//Now Check for the row count
if($stmt->rowCount === 1) {
    echo "Account Exists";
} else {
    echo "Account does not exist";
}
Related Question