How to avoid duplicate register username in PHP

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

User avatar
jackass007
Posts: 18
Joined: Tue Mar 16, 2010 5:38 pm UTC

How to avoid duplicate register username in PHP

Postby jackass007 » Tue Mar 16, 2010 6:06 pm UTC

hello..i'm a beginner of php. actually, im developing one system by using php coding and now facing the problem that frustrated me.
Hope some one can gv me a hand to help me solve the problem.

Web connector that im using:
AppServ Open Project 2.5.10
DAtabase
phpMyAdmin Database Manager Version 2.10.3

Problem:for an example:- Once a user hv registered the username as ABC, and the registration process has successfully.
however, when the another user register the username same as ABC again, the system won't prompt out the error message, since there is duplicated the username in my database.

so , anyone can give me some ideas how to avoid or fix my problem?!

User avatar
bocochoco
Posts: 317
Joined: Thu Aug 06, 2009 8:22 pm UTC

Re: How to avoid duplicate register username in PHP

Postby bocochoco » Tue Mar 16, 2010 6:20 pm UTC

First you should search the database to see if that user exists. If it doesn't exist, you can insert.

Code: Select all

$username = mysql_real_escape_sequence($_GET['username']);

$q = "SELECT id, username FROM users WHERE username = '$username'";
$results = @mysql_query($q);
if(mysql_num_rows($results) > 0)
{
  // Username exists.
  echo "Error: Username already taken.";
}
else
{
  // Username available.
}


Basically this queries the database to check if the username exists by pulling any rows that match that username. Then counts the number of rows returned. If 0 rows are returned, then the username is available, otherwise it is taken by another user. Of course you will have to change this to work with your database and form, but this should give you a good idea of how to do it. Hope this helps
Last edited by bocochoco on Wed Mar 17, 2010 3:01 pm UTC, edited 1 time in total.
Image

joeframbach
Posts: 1478
Joined: Sun Nov 05, 2006 12:49 am UTC

Re: How to avoid duplicate register username in PHP

Postby joeframbach » Tue Mar 16, 2010 6:51 pm UTC

For absolute certainty that this won't happen, create a UNIQUE constraint on the table.

User avatar
bocochoco
Posts: 317
Joined: Thu Aug 06, 2009 8:22 pm UTC

Re: How to avoid duplicate register username in PHP

Postby bocochoco » Tue Mar 16, 2010 6:59 pm UTC

joeframbach wrote:For absolute certainty that this won't happen, create a UNIQUE constraint on the table.

I've never tried that, I've always enforced it myself. What does mysql return if you try and add a duplicate value on a table with a UNIQUE constraint?
Image

User avatar
Berengal
Superabacus Mystic of the First Rank
Posts: 2707
Joined: Thu May 24, 2007 5:51 am UTC
Location: Bergen, Norway
Contact:

Re: How to avoid duplicate register username in PHP

Postby Berengal » Tue Mar 16, 2010 7:06 pm UTC

It returns an error, as it should do.
It is practically impossible to teach good programming to students who are motivated by money: As potential programmers they are mentally mutilated beyond hope of regeneration.

User avatar
phlip
Restorer of Worlds
Posts: 7569
Joined: Sat Sep 23, 2006 3:56 am UTC
Location: Australia
Contact:

Re: How to avoid duplicate register username in PHP

Postby phlip » Wed Mar 17, 2010 1:07 am UTC

bocochoco wrote:

Code: Select all

$q = "SELECT id, username FROM users WHERE username = '" . mysql_real_escape_string($username) . "'";

FTFY. Also, I'd probably use SELECT COUNT(*) FROM etc, instead of just selecting them and counting the rows... but that's just me.

But yeah - throw in a UNIQUE index on there. It's one of the few constraints that MySQL will actually enforce, for the default engine, may as well take advantage of that. If you try to insert a new user that matches an existing one, you'll get an error (mysql_query() will return FALSE), and mysql_errno() will return ER_DUP_ENTRY.

Code: Select all

enum ಠ_ಠ {°□°╰=1, °Д°╰, ಠ益ಠ╰};
void ┻━┻︵​╰(ಠ_ಠ ⚠) {exit((int)⚠);}
[he/him/his]

User avatar
Pesto
Posts: 737
Joined: Wed Sep 05, 2007 5:33 pm UTC
Location: Berkeley, CA

Re: How to avoid duplicate register username in PHP

Postby Pesto » Fri Mar 19, 2010 1:38 am UTC

bocochoco wrote:
joeframbach wrote:For absolute certainty that this won't happen, create a UNIQUE constraint on the table.

I've never tried that, I've always enforced it myself. What does mysql return if you try and add a duplicate value on a table with a UNIQUE constraint?

It's an important best practice. Imagine you and I both try to register the same username simultaneously. Each of us will query the user table, finding the username absent, then both inserts will succeed and you will end up with two people with the same username. Sure, in the case of user registration, it's very unlikely, but any time you employ this pattern you run the risk of duplicates. Best to simply enforce uniqueness.

Aren't race conditions fun?

Axidos
Posts: 167
Joined: Tue Jan 20, 2009 12:02 pm UTC
Location: trapped in a profile factory please send help

Re: How to avoid duplicate register username in PHP

Postby Axidos » Fri Mar 19, 2010 12:10 pm UTC

phlip wrote:
bocochoco wrote:

Code: Select all

$q = "SELECT id, username FROM users WHERE username = '" . mysql_real_escape_string($username) . "'";

FTFY. Also, I'd probably use SELECT COUNT(*) FROM etc, instead of just selecting them and counting the rows... but that's just me.

But yeah - throw in a UNIQUE index on there. It's one of the few constraints that MySQL will actually enforce, for the default engine, may as well take advantage of that. If you try to insert a new user that matches an existing one, you'll get an error (mysql_query() will return FALSE), and mysql_errno() will return ER_DUP_ENTRY.

Escaping? Queries straight from code? I thought stored procedures were all the rage now. Are you deliberately keeping things simple or are they not as much the rage as I had been informed that they are?

On a side note, I am confused about escaping when it comes to stored procedures. Where does escaping occur there?
Do you do it:
a. when calling the procedure (I believe this one is a definite "no")
b. within the stored procedure itself
c. never, and let the database software sort it out in some miraculous way we don't have to worry about

User avatar
Aaeriele
Posts: 2127
Joined: Tue Feb 23, 2010 3:30 am UTC
Location: San Francisco, CA

Re: How to avoid duplicate register username in PHP

Postby Aaeriele » Fri Mar 19, 2010 1:30 pm UTC

Axidos wrote:Escaping? Queries straight from code? I thought stored procedures were all the rage now. Are you deliberately keeping things simple or are they not as much the rage as I had been informed that they are?

Stored procedures not so much as just prepared statements.
Vaniver wrote:Harvard is a hedge fund that runs the most prestigious dating agency in the world, and incidentally employs famous scientists to do research.

afuzzyduck wrote:ITS MEANT TO BE FLUTTERSHY BUT I JUST SEE AAERIELE! CURSE YOU FORA!

User avatar
phlip
Restorer of Worlds
Posts: 7569
Joined: Sat Sep 23, 2006 3:56 am UTC
Location: Australia
Contact:

Re: How to avoid duplicate register username in PHP

Postby phlip » Fri Mar 19, 2010 1:34 pm UTC

Axidos wrote:Escaping? Queries straight from code? I thought stored procedures were all the rage now. Are you deliberately keeping things simple or are they not as much the rage as I had been informed that they are?

It's been years since I last did anything serious with MySQL... last I checked, SPs were still just something to look forward to, but not yet a good idea to rely on them... and prepared parameterised queries still a pipe dream. So I don't really know how they work in MySQL... I can only guess based on other DBMSes.

Axidos wrote:On a side note, I am confused about escaping when it comes to stored procedures. Where does escaping occur there?

Well, depends how you call them. If you're still building an SQL string, like

Code: Select all

$query = "EXECUTE PROCEDURE SP_MAGIC($param1, $param2)";
then you'll still need to be doing the escaping yourself. If there's something where you're passing the query and the values as separate parameters to some API call (like, say, a prepared query), then the API call should be doing any necessary escaping for you (but double-check the doc).

There'd be no reason to do escaping within the SP, unless you're building SQL queries in string variables inside your SP and then dynamically executing them (please don't ever do this).

Code: Select all

enum ಠ_ಠ {°□°╰=1, °Д°╰, ಠ益ಠ╰};
void ┻━┻︵​╰(ಠ_ಠ ⚠) {exit((int)⚠);}
[he/him/his]

User avatar
Aaeriele
Posts: 2127
Joined: Tue Feb 23, 2010 3:30 am UTC
Location: San Francisco, CA

Re: How to avoid duplicate register username in PHP

Postby Aaeriele » Fri Mar 19, 2010 7:12 pm UTC

Both PDO and mysqli provide excellent prepared statement support with parametrization, and both are present in most PHP5+ environments.
Vaniver wrote:Harvard is a hedge fund that runs the most prestigious dating agency in the world, and incidentally employs famous scientists to do research.

afuzzyduck wrote:ITS MEANT TO BE FLUTTERSHY BUT I JUST SEE AAERIELE! CURSE YOU FORA!

User avatar
jackass007
Posts: 18
Joined: Tue Mar 16, 2010 5:38 pm UTC

Re: How to avoid duplicate register username in PHP

Postby jackass007 » Sun Mar 21, 2010 9:50 am UTC

thx all ur comments..i hv changed the username table to UNIQUE..it's worked.
but now i dont know how to display the error message since the user name duplicated happen in time...

User avatar
Pesto
Posts: 737
Joined: Wed Sep 05, 2007 5:33 pm UTC
Location: Berkeley, CA

Re: How to avoid duplicate register username in PHP

Postby Pesto » Sun Mar 21, 2010 5:47 pm UTC

jackass007 wrote:thx all ur comments..i hv changed the username table to UNIQUE..it's worked.
but now i dont know how to display the error message since the user name duplicated happen in time...

mysql_query() returns the boolean value false when it encounters an error. You'll need to check the return value to detect the error.

Something along these lines.

Code: Select all

$sql = "some insert query";
$result = mysql_query($sql, $db);
if($result === FALSE) {
   $error_number = mysql_errno($db);
   $error_message = mysql_error($db);
}


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 9 guests