检查数据库中是否已存在用户名

Question

I'd like to make an if statement that checks if the username, already exists in the MYSQL database. I tried some different stuff, but i cant make it work. Every time I test it in my browser I get a notice

Notice: Undefined index: username in etc.

I am confused if it has anything to do with the $result variable or the $check variable or neither.

Here is the HTML form and the PHP script. https://gist.github.com/anonymous/9704354

Thank you and have a nice weekend!

Answer 1

There are a few things that are wrong in your code.

First, never place variables directly in SQL queries, thats how SQL injections happen. Start using PDO or another library for your MYSQL.

The reason you are getting an undefined notice is because of this line.

 $result = mysql_query("SELECT * FROM users WHERE username = '$_POST[create_user]'");

It should be this without fixing the huge SQL Injection flaw

 $result = mysql_query("SELECT * FROM users WHERE username = '{$_POST['create_user']}'");

Also you should add a "LIMIT 1" to the end of the select query to speed things up. No need looking for more than one user.

You can verify the user by just checking for row_count instead of checking the text values. Since MySQL is not case sensitive for some fields, username "AAAaaa" will be equal to "aaaAAA". If you check row count instead, you will be sure that no usernames are in the database of that text. Or if you want to check using PHP, make sure you pass the usernames through strtolower()

When you start using PDO, the following example will help you.

$dbh = new PDO() // Set the proper variables http://us2.php.net/pdo
if(empty($_POST['create_user'])) {
   echo 'Username is Empty. Always check if POST and Get data is set';
   die();
}

$query = "SELECT * FROM `users` WHERE `username` = ? LIMIT 1;"
$data = array($_POST['create_user']);
$sth = $dbh->prepare($query);

if(!$sth->execute($data)) {
   echo 'Handle SQL Error';
   die();
}

if($sth->rowCount() == 0) {
   echo 'Unused Username';
}else{
   echo 'Used Username';
}