[RESOLVED] what's the best way to makew sure logins are unique - and automatically alter them so

[biggus]

I have a site where people can add members. THe login must be unique, naturally. And, since ai want the use to just sit back and let the machine do the work, I'm trying to figure out how to increment the login by 1 until it is unique. The logins are store in a mysql db. I tried the following while loop, but it went forever:

$SQL="select count(*) as howmany from mytable where login='$login'";
$result=$db->Execute($SQL);
$howmany=$result->fields["howmany"];
$count=$howmany;
while($howmany>0){
  $login.=$count;
  $SQL="select count(*) as howmany from mytable where login='$login'";
  $result=$db->Execute($SQL);
  $howmany=$result->fields["howmany"];
  $count++;
}

Since this is inside a function, I also tried using static and global (separately) vars to track the count number that would be appended to the login. I'd realy like the logins to increment like the following:

biggus
biggus1
biggus2
etc

Anyone have any tips? Maybe it's because today is Friday, but I just can't seem to figure this one out.

Thanks,
Biggus

[konsu]

hello,

given a login name $login, do this:

1. check if $login exists in the database
2. if step 1 fails (there is no $login) go to step 5
3. generate new $login
4. go to step 1
5. use $login

[biggus]

that's what's I'm doing. I just didn't put the return line in there. The thins is, I want the generated login to be the user's chosen login incremented by 1 until it is unique.

So, if login 'Biggus' is in the db, then try 'Biggus1'. If 'Biggus1' is there, then try 'Biggus2'...

I even tried using a session variable to track the increment var. Didn't work.

Man, am I ever looking forward to the weekend!

[konsu]

1. you do not need to count anything in your select statement, just check if $login is in database. use select * from ... where login=... and see if this returns more than zero rows.
2. line $login .= $count changes $login variable, this is not needed if you want to check 'Bigus1', then 'Bigus2', etc. what you have now is 'Bigus1', 'Bigus12', 'Bigus123'.

[leatherback]

So yuou'd need to change this:
$login.=$count;
$SQL="select count(*) as howmany from mytable where login='$login'";

to:

$logintest=$login.$count;
$SQL="select count(*) as howmany from mytable where login='$logintest'";

Make sure you set $ logintest to $login for the first query

[maxpup979]

If you are running a transactional system like Postgres, this is easy. query the DB for the next value in a serial, while in a transaction. this prevents another simultaneous user from getting the same number. In a non transactional system, its going to be very difficult. If you have 2 users that are both getting IDs at the same time, the counts will be the same, and the login will not be unique. in that case, when you do the write, you need to verify that the ID is unique, and increment, and test again etc.

[biggus]

thanks everyone. I figured out the problem. I had the increment ($count++) in the wrong spot. It needed to be BEFORE the query, with the query written as such:

  $SQL="select count(*) as howmany from mytable where login='$login.$count'"; 

I missed leatherback's post, but ended up writing the pretty much the same code as was posted.

Thanks again,
Biggus

[leatherback]

NP. Mark the thread resolved if you still see this.

J.

[biggus]

oop. sorry about that. I reckon I plum forgot. Shucks :o

[mrhappiness]

funny thing is:
if you did not tell the database that login has to be UNIQUE, something like this may happen:

1. user #1 wants to sign up using abc as login name

2. user #2 wants to sign up using abc as login name (yes, it's the same login name)

3. user #1 (the instance of the script that's processing his signup) queries the database and finds no entry with abc as login

4. user #2 (the instance of the script that's processing his signup) queries the database and finds no entry with abc as login

5. user #1 gets inserted with abc as login

6. user #2 gets inserted with abc as login

7. you got duplicates

if you did tell the database that login has to be UNIQUE, something like this may happen:

1. user #1 wants to sign up using abc as login name

2. user #2 wants to sign up using abc as login name (yes, it's the same login name)

3. user #1 (the instance of the script that's processing his signup) queries the database and finds no entry with abc as login

4. user #2 (the instance of the script that's processing his signup) queries the database and finds no entry with abc as login

5. user #1 gets inserted with abc as login

6. user #2 is to inserted with abc as login using mysql_query but it doesn't work because of the UNIQUE KEY

7. you check the success of the INSERT using mysql_error and inform user #2 to choose another login

8. you got no duplicates

quite funny, isn't?
all you have to do is add a UNIQUE KEY (that is: tell mysql that this field may only hold unique values), INSERT as usual and check with mysql_error

btw: scenario #1 shows the difficulties you may encounter with those so-called race conditions

[leatherback]

Hi MrHappyness,

I would really like to see the odds on:
- People using the sasme username
- People signing up on the same instant

People signing up on the same instant with the same username.

Granted. It COULD happen. And I do not think that creating similar names this way is good, since the user is not aware of the issue. (I use the technique for naming images in picturealbums). But still, I think the risk of your server crashing before the day is over is abaout the same, depending your system setup (If you are on M$ h*t like me, changes that your server will crass are much bigger 😃 )

[mrhappiness]

of course chances are very little but

1. don't setting a UNIQUE KEY directly in the database won't protect you 100% from getting duplicate entries (alas you don't LOCK and UNLOCk your tables)

2. doing it the UNIQUE KEY way is 1 single statement and checking mysql_error afterwards
   doing it the SELECT and INSERT way is (under normal circumstances) 2 statements

   i'm off the opinion that using 1 statement is better than using 2 statements with 1 being the one that can be use without the other one as well

btw: i'm on MS and my system doesn't crash, seems like it's afraid of me getting angry 😃

btw #2: my apache runs on linux 🙂