username numbering

ATLien

I have a user authentication script that derives a username as:

$username = strtolower( $first . "_" . $last);

I then have a script that checks to see if the username is taken. If it is then I want it to add a 2, 3, 4 to the end. Like if the username is taken then assign the $username as

$username = strtolower( $first . "_" . $last "2");

except I don't want it to just do 2 I want it to change it to 1 integer up from the current one.
I.E if there is 2 users in the database one is lentz_chris and the other is lentz_chris2 then I want the script to make this persons $username lentz_chris3. I think you get the picture. Here is my code:

$username = strtolower( $first . "_" . $last);

$check = mysql_query("Select * from agents where agent_username = '$username'") or die ("Mysql error: " . mysql_error());

if (mysql_num_rows($check) > 0){
	$username = strtolower( $first . "_" . $last . "2");}

That code only works for a 2nd user with the same name.

dalecosp

$username = strtolower( $first . "_" . $last); 

$check = mysql_query("Select * from agents where agent_username = '$username'") or die ("Mysql error: " . mysql_error()); 

while (mysql_num_rows($check) > 0){ 
   if (!$c) { $c=2; }
    $username = strtolower( $first . "_" . $last . $c);
   $c++;}

HalfaBee

I think this is more like what you want.

It names the next user with the same first/last name as first_last1 and if another user has the same name it becomes first_last2.

HalfaBee

$username = strtolower( $first . "_" . $last); 

$check = mysql_query("Select * from agents where agent_username LIKE '$username%'") or die ("Mysql error: " . mysql_error()); 

if ($num=mysql_num_rows($check) > 0){ 
    while( $row=mysql_fetch_array($check) !==FALSE );


$newname = $username . $num;
while( in_array( $newname , $row ) )
{
    $num++;
    $newname = $username . $num;
}
 )


}

edit: fixed the % and made sure name was unique.
Probably should have reused deleted names, but who cares.

dalecosp

The lazy man always finds the best way!

You lazy fool, you! 🙂 Assuming that the SQL will do it
right, (I've not liked some of MySQL LIKE and REGEXP's results) that's much more efficient ...
'kludge' is my middle name... 😉

HalfaBee

I havn't had any trouble with using LIKE, but my data is normally very well formattted machine made stuff.

This seemed the easiest way to do it. 🙂

HalfaBee

ahundiak

I'm not sure but I think you need a % after $username to get the LIKE comparision to work as expected. Maybe not, I couldn't find a description of LIKE in the mysql manual.

Also, be careful about relying on the number of rows to generate the number. Say you have three users names user1,user2 and user3. Now user2 is deleted. That leaves 2 records so adding yet another user would result in a duplicate of user3.

Regardless of how you do it, make sure you create an unique index on username. That will prevent duplicates no matter what.

HalfaBee

Thanks. I new I forgot something.
Fixed both probs.

HalfaBee

dalecosp

HalfaBee ... you know I'm teasing with the 'fool' thing...good code... 🙂

&& of course, Ahundiak shines again ... I bet that most of my SQL LIKE's forgot that operator, too...

back to the scripts!!!

HalfaBee

After I posted it, I thought I had forgot the %, the phone rang and I forgot to fix the first forgotten thing!

I didn't think you were being mean and horrible. 🙂

It's amazing how other people can always see your mistakes.

HalfaBee

ATLien

thatnks guys.

fikse

I am trying to use the code that HalfaBee posted and am getting the following error:

Warning: in_array(): Wrong datatype for second argument in ** on line 29

line 29 is:

while( in_array( $username , $row ) )

any ideas?

thanks

HalfaBee

I think this line needs changing
while( $row=mysql_fetch_array($check) !==FALSE );

to
while( $row[]=mysql_fetch_array($check) !==FALSE );

HalfaBee

fikse

thanks for the help, I tried changing that line and the script just hangs now....

any other suggestions?

thanks

HalfaBee

I found this caused problems

while( $row=mysql_fetch_array($check) !==FALSE );
replace with
while( $row=mysql_fetch_array($check) );

Put a few debugs in and find where it hangs.

HalfaBee

drawmack

A better way is to use count

$sql = "SELECT count(agent_username) from adents where agent_username LIKE \"$username%\"";

$query = mysql_query($sql) or die ("Mysql error: " . mysql_error());

$exists = mysql_result($query,0,0);

mysql_free_result($query);

$exists--;

$newname = $username . $exists;

Got rid of all loops, much more efficient.

HalfaBee

Been over this before.
If you use count to determine the next number you can end up with the wrong result if usernames have been deleted.

HalfaBee

drawmack

but how is looping and adding 1 to $i for each match any different then selecting the count?

and you're deleting usernames because? you should have a disable option but a username should never be deleted.

Anyway yo uare correct but you can still do it without loops but using select max(primary_key) where and then using that result to get the actual data like this

$sql = "SELECT MAX(primary_key) FROM users WHERE username like \"$username%\";

if($query = mysql_query($sql)) {
  $sql = "SELECT * FROM users WHERE primary_key = " . mysql_result($query,0,0);
   mysql_free_result($query);
   $query = mysql_query($sql);
   $highest = mysql_result($query,0,'username');

   //I have to go now but at this point you would reg ex for the number
  //then you would increment that number append it to username and 
  //you would be all done.
  //make sure you account for the fact that the first one
  //will not have a number on the end though.
} 

//username is correct if the if doesn't get fallen into.

laserlight

Actually, what if 2 usernames tables were used, since duplicates are expected?

Table: agent_usernames
users_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
name_count INT NOT NULL
username VARCHAR(n)

Table: agents
agent_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
users_id INT NOT NULL
name_id INT NOT NULL
//other columns

Every time a new user is added, the agent_usernames table is checked for the name that corresponds to the given username.
If that is, then the name_count for that row is incremented, and inserted into agents as name_id, with agents.users_id corresponding to the found agent_usernames.users_id
If agent_usernames does not contain the given username, a new row is inserted with name_count=1

This may have a few problems though.
The first agent on the list will have '1' appended to his name, unless that case is accounted for.
Also, if the user manages to get a alphanumeric username to start off with, this could potentially cause conflicts with existing usernames, not when storing, but when displaying.

drawmack

I have devised a solution that uses one table and some creative selecting mixed with regular expresions to achieve the desired result. Hey when you come up with a fun problem I'm more then happy to help solve it.

Attached to this post you'll find a zip file which contains two other files and php script and a sql file. Run the sql file into your database (warning if you have a users table in the database already then rename it first) configure the php script variables to access your database and you'll have a complete working example of this methodology.

Here is the function that is the meat of the solution:

 function makeUser($fn,$ln,$pswd) {
   //generate default username
   $username = $fn . "_" . $ln;

 //see if another user already has the default username and grab the ID
 //if the newest record with a base username equal to the default username
 $sql = "SELECT MAX(ID) FROM users WHERE Username LIKE \"$username%\"";
 if($query = mysql_query($sql)) {

   //get the most recent record who has a base username equal to the default username
   $sql = "SELECT * FROM users WHERE ID=" . mysql_result($query,0,0);

	 //end the first query
	 mysql_free_result($query);

	 //if we have a record to look at
	 if($query = mysql_query($sql)) {
	   //get the username from the retrieved record
	 $lastun = mysql_result($query,0,'Username');

		 //if the username has a number on the end get that number into $num[0] 
		 //otherwise set isNum to false
		 $isNum = ereg("[0-9]+",$lastun,$num);

		 //if this is the second user with this base name append a 1 to the end
	 if(!$isNum) {$username .= "1";}
		 //otherwise increment the highest existing number and append that to
		 //the default username.
  	 else {$username .= $num[0] + 1;}
	 }
 }

 //insert the new user into the database
 $sql = "INSERT INTO users (FirstName,LastName,Username,Password) VALUES (";
 $sql .= "\"$fn\",\"$ln\",\"$username\",\"$pswd\")";

 //report success or failure to the user
 if(mysql_query($sql)) {echo "<div><br />Successfully Entered New User<br /><br /></div>\n";}
 else {echo "<div class=\"error\"><br />Error Inserting User<br />$sql</font><br /><br /></div>\n";}
 } //end makeUser

I've done a pretty good job comenting it but if you want anything explained just let me know.

In the attached zip file, the sql file's indentation maybe a little off my editor uses spaces sometimes and tabs sometimes. It's annoying really and I have a tool to fix it but since this is only about ten lines of very simple sql I didn't see the need. The php script has been fixed up. If the sql gives you an error on the creat table try removing the if not exists. Other then that this example should be very easy to set up and run.

drawmack

no response? did it work? is this resolved? which method did you sue?