How do I check for existing username

Question

I would like to see if a username is already created... I seached, and the mysql_num_row() or whatever it is, doesn't seem to work, i get a T_STRING Error if i use that... Heres my code, that says it should work, but it doesn't, why?:

/*simple checking of the data*/
if(isset($_POST['login']) && isset($_POST['pass']) && ($_POST['pass'] == $_POST['confirm']))
{

/*Connection to database logindb using your login name and password*/
$db=mysql_connect('localhost','user','pass') or die(mysql_error());
mysql_select_db('db');

/*additional data checking and striping*/
$_POST['login']=mysql_real_escape_string(strip_tags(trim($_POST['login'])));
$_POST['pass']=mysql_real_escape_string(strip_tags(trim($_POST['pass'])));

mysql_query("INSERT INTO profiles SET username='{$_POST['login']}',password='{$_POST['pass']}'",$db);

/*If the database has been updated*/
if(mysql_affected_rows() > 0)
{
    $_SESSION['login'] = $_POST['login'];
    $login='Welcome '.$_SESSION['login'];
}
else
{
    $login= 'This login name already exists.';
}

mysql_close($db);

}

And i did as showdev suggested but get an error:

New code:

/*additional data checking and striping*/
$_POST['login']=mysql_real_escape_string(strip_tags(trim($_POST['login'])));
$_POST['pass']=mysql_real_escape_string(strip_tags(trim($_POST['pass'])));

mysql_query("INSERT INTO profiles SET  username='{$_POST['login']}',password='{$_POST['pass']}'",$db);

/*If the database has been updated
if(mysql_affected_rows() > 0)
{
$_SESSION['login'] = $_POST['login'];
$login='Welcome '.$_SESSION['login'];
}
else
{
$login= 'This login name already exists.';
}*/
$sql="SELECT COUNT(*) FROM `profiles` WHERE `username`='$username' AND `password`='$password' LIMIT 0,1;"
$q=mysql_query($sql) or die(mysql_error());
$r=mysql_fetch_row($q);
if ($r[0]==0) {
   // insert new user
    } else {
   // user already exists
    }

mysql_close($db);

}

Error:

Parse error: syntax error, unexpected T_VARIABLE in /home/teachert/public_html/php/register.php on line 34

UPDATE:

New Code:

mysql_query("INSERT INTO profiles SET `username`='{$_POST['login']}',`password`='{$_POST['pass']}' ON DUPLICATE KEY UPDATE `username`='{$_POST['login']}'",$db);
/*If the database has been updated*/
if(mysql_affected_rows() == 1)
{
    $_SESSION['login'] = $_POST['login'];
    $login='Welcome '.$_SESSION['login'];
}
else
{
    $login= 'This login name already exists.';
}

mysql_close($db);

}

Still doesn't work

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Apr 26 '13 at 23:03

showdev · Answer 1 · 2013-04-26T16:41:28.577

0

You may want to SELECT the username to see if it already exists before INSERT. Something like this:

$login=mysql_real_escape_string(strip_tags(trim($_POST['login'])));
$pass=mysql_real_escape_string(strip_tags(trim($_POST['pass'])));

$sql="SELECT COUNT(*) FROM `profiles` WHERE `username`='$login';"

$q=mysql_query($sql) or die(mysql_error());
$r=mysql_fetch_row($q);

if ($r[0]==0) { // count is 0, no matching users

   // insert new user
   $sql="INSERT INTO `profiles`
           (`username`,`password`) VALUES ('$login','$pass');";
   mysql_query($sql) or die(mysql_error());

   $_SESSION['login'] = $login;
   $login='Welcome '.$_SESSION['login'];

} else {

   // user already exists
   $login= 'This login name already exists.';

}

Also, consider PDO since mysql_* commands are depreciated.

edited Apr 26 '13 at 16:41

answered Apr 26 '13 at 00:40

showdev

25,529
35
47
67

1

Consider removing `AND password='$password'`. – leonardo_assumpcao Apr 26 '13 at 00:42
I did that but get an error such as: Parse error: syntax error, unexpected T_VARIABLE in /home/teachert/public_html/php/register.php on line 34 – James Apr 26 '13 at 00:44
@leonardo_assumpcao Good point, assuming there should not be duplicate usernames. – showdev Apr 26 '13 at 00:45
@James That looks like a PHP parse error, not a mysql error. Probably because you are not using variables called `$username` or `$password`. I have updated my code. – showdev Apr 26 '13 at 00:51
ok so now here is my code: mysql_query("INSERT INTO profiles SET `username`='{$_POST['login']}',`password`='{$_POST['pass']}' ON DUPLICATE KEY UPDATE `username`='{$_POST['login']}'",$db); /*If the database has been updated*/ if(mysql_affected_rows() == 1) { $_SESSION['login'] = $_POST['login']; $login='Welcome '.$_SESSION['login']; } else { $login= 'This login name already exists.'; } mysql_close($db); } – James Apr 26 '13 at 01:01
@James Please update your question. Code is difficult to read in comments. If you use the "ON DUPLICATE KEY" method, you will need to set the `username` column to be [`UNIQUE`](http://stackoverflow.com/questions/5038040/mysql-make-a-field-unique). Otherwise, there will never be a duplicate key. – showdev Apr 26 '13 at 01:08
1

@showdev Just to clarify, `mysql_affected_rows()` does return a value for `INSERT` statements (as per [the documentation](http://php.net/manual/en/function.mysql-affected-rows.php)). You may want to update your answer. – Nerdwood Apr 26 '13 at 01:11
@Nerdwood That's weird. I copied my description directly from that same php.net page. Oh well, I will update my answer. Thanks! – showdev Apr 26 '13 at 01:14
No probs. :) I do that too! ;) – Nerdwood Apr 26 '13 at 01:16
@Nerdwood I guess that means that `mysql_affected_rows()` will return 1 regardless of whether it `INSERTS` or `UPDATES`. So method #2 won't work as expected. I'll adjust my answer accordingly. – showdev Apr 26 '13 at 01:17
1

@showdev Yep, both `INSERT`s and `UPDATE`s will return a result using `mysql_affected_rows()`, and both will be > 0 if they made a change. – Nerdwood Apr 26 '13 at 01:27
1

If you design your DB table well there is absolutely no reason that you need to have an extra `SELECT` before making an `INSERT` for such a case. Just provide a unique index on `username` and attempt the `INSERT` it will fail if the username already exists. Saving you the need to two two separate queries in most cases. You just need to handle the query failure as an expected use case. – Mike Brant Apr 26 '13 at 23:33
@MikeBrant I was trying to accomplish that using `UNIQUE` and `ON DUPLICATE KEY` to prevent duplicate entries. The trick was how to determine whether the user was inserted or already existed. But I forgot that, even *without* using `ON DUPLICATE KEY`, a failed query would indicate that the username exists and a successful query would indicate that the username was added. For some reason, that concept escaped me before. Thanks for the reminder. – showdev May 02 '13 at 18:55

score -1 · Answer 2 · edited Apr 26 '13 at 23:02

-1

This checks if user already exists, and uses the sanitize function to prevent injection

function sanitize($string) {
  $string = mysql_real_escape_string(strip_tags(trim($string)));
  return $string;
}

function user_exists($username) {
  $username = sanitize($username);
  return (mysql_result(mysql_query("SELECT COUNT(`username`) FROM `profiles` WHERE `username` = '$username'"), 0) == 1) ? true : false;
}

if (user_exists() === true) {

} else {
  $login= 'This login name already exists.';
}

edited Apr 26 '13 at 23:02

showdev

25,529
35
47
67

answered Apr 26 '13 at 22:43

user2058217

61
5

1

Ugh... use prepared statements, FFS – hd1 Apr 26 '13 at 23:43
All `mysql_*` functions are [**officially deprecated**](http://j.mp/XqV7Lp) *(no longer supported/maintained)* and will be [**removed**](http://j.mp/11j2t6j) in the future. You should update your code using [PDO](http://php.net/pdo) or [MySQLi](http://php.net/msqli) to ensure future functionality. – cryptic ツ Apr 28 '13 at 00:43

How do I check for existing username

2 Answers2