Username Increment

[hairpuller]

I am wanting to increment usernames which are to be alphabetic characters A-Z or a-z and if someone uses "abc" it will automatically be given "abc1" and the next applicant to apply for that same username will automatically be given "abc2", the next "abc3" and so on. If I enter the following into MySQL;

SELECT username FROM users WHERE username REGEXP 'abc[0-9*]';

the result is

abc1
abc2
abc3

I have considered the COUNT () or mysql_num_rows process in MySQL but foresee that would not work if for example "abc2" was deleted the number of rows found would be 2 then the next applicant would get "abc3" a duplicate username which would be unacceptable.

Can anybody advise if and how PHP could select the "last" or "bottom" row or the "greatest value" and then detect the numeric characters, create a new row, insert the username adding 1 to the value?

I have searched the MySQL manual and also emailed MySQL suggesting a "feature request" but they replied that "it would not be of very high priority", which sounds as if it can not currently be facilitated within MySQL.

I have also searched the PHP manual on "Incrementing/Decrementing Operators" but more in particular "MySQL Functions" and can't find anything there that matches what I'm trying to achieve.

Regards, David Young.

[etully]

MySQL won't add the feature because that function is exactly the kind of function that languages like PHP were designed for.

One problem with your routine is that if some user comes along and requests their username to be "abc9999999999999", then the next number would be "abc10000000000000" which would be unfortunate if "abc3" was still available.

This is what I would do:

select username FROM users WHERE username like 'abc%' ;

and the result might be:

abc1
abc2
abc77
abc9999999999999999

Put the results of the query into an array. Set a counter like $i and check to see if the username "abc" + $i is in the array. If yes, then move on to the next higher value of $i and check again. If it's not in the array, then you can automatically assign this username to the customer.

This should find a good username for them very quickly unless you have billions of users whose username starts with "abc". This solution would also be a problem if your system was creating tens of thousands of new users per second. Neither of those scenarios are very likely so this solution is pretty safe.

[laserlight]

I think the best method is to just inform the user that the username is taken and get the user to choose a new username. Consider that superstitious users may get rather upset if they are saddled with a username with an "unlucky" number appended.

[etully]

The solution I've seen Yahoo use is to suggest a few possible usernames. The user can select one of them or try a different one.

Their suggestions put the current Year at the end and the user's Zipcode at the end so you would see:

sorry, "abc" is taken. Maybe you would like:

( ) abc13210
( ) abc2007
( ) abc666
( ) other:__________________________________

[hairpuller]

Thanks etully & laserlight for your comments. I currently have the system of asking the user to choose another username but my own personal experience of that is each one I try has already been taken and found joining up to some sites frustrating to say the least. I had also considered the Yahoo method but I believe the method I have in mind would be the least frustrating but I take the point of the user receiving a number that they may feel uncomfortable with but there would be the provision of them changing their username in whole or applying again to receive the next number. Any cancelled usernames I intend not to be used again, but I am not yet sure how I'm going to achieve that. As to the multiple numbers scenario a user may apply, I intend to limit the applied username to alphabetic and no numbers, but I'm not sure how to eliminate the charactors other than alphanumeric. I had considered selecting like 'abc%' but felt the REGEXP 'abc[0-9*]' was the better.

I know extremely little about arrays and wonder if you could start me off with some coding? It would not need to be too accurate as I would be able to play around with it to achieve the finer detail.

Regards, David.

[etully]

//  Assume that the previous page has a form that asks the customer
//  to enter their preferred username and is passed in as "name"

$name = $_REQUEST['name'];

//  Prepare a variable to hold an array 

$usernames = array();

//  Query the database for any usernames that start with that value

$query = "select username FROM users WHERE username like '$name%'";
$result = MYSQL_QUERY($query); 

//  Loop through all the results and put them into the $usernames array

while (list($user) = @mysql_fetch_row($result)) { $usernames[] = $user; }

//  First, check to see if the user's first choice is already being used
//  Notice the ! sign which means "not".  Is $name NOT in $usernames array?

if (!in_array($name,$usernames)) {
	print "congratulations, your first choice is NOT being used.";
	$query = "insert into database... store the user data";
	// yada yada yada
}
else { // If you got here,  then their first choice WAS already being used
	$i = 1;
	while ($i < 1000) {
		$check_this_one = $name . "$i";
		if (!in_array($check_this_one,$usernames)) {
			print "You could have $check_this_one if you want it";
			break;  //  gets us out of the while loop
				//  Note:  If we don't "break" our of the loop
				//  then $i goes up one and we check the next #
		}
	$i++;
	}

if (!in_array($name.$zipcode,$usernames)) {
	echo "Note that ".$name.$zipcode." isn't being used yet.  You could have it";
}

if (!in_array($name.date("Y"),$usernames)) {
	echo "How about this one: ".$name.date("Y")." it's not being used yet either";
}
}

[hairpuller]

Thanks etully, I'll work on that and get back to you when I succeed.

[laserlight]

I intend to limit the applied username to alphabetic and no numbers, but I'm not sure how to eliminate the charactors other than alphanumeric. I had considered selecting like 'abc%' but felt the REGEXP 'abc[0-9]' was the better.

I suggest checking that the username is valid with PHP. Querying the database would only be to check that the username has not been taken in any possible form. Instead of enforcing a strictly alphabetic username, you could relax the constraint to allow an alphanumeric username where the last character is not a digit. [man]preg_match/man with a pattern such as /^\w[a-z]$/i could be suitable.

[hairpuller]

Hi etully, I hope you are still logging on to this site. Over the last few weeks I have had other things to do but I'm back into this project. I have studied your code and rather that search every possible numeral I think it would be much simpler to process the search in the following method, that is if it is possible. I am only guessing so there will be no accuracy in this code but you should be able to follow my thinking.

// Assume that the previous page has a form that asks the customer
// to enter their preferred username and is passed in as "user"

$user = $_REQUEST['user'];

// Query the database for any usernames that start with that value and widcard number

$query = "SELECT username FROM users WHERE username REGEXP '$user[0-9*]'";

$result = mysql_query($query);

if (!$result($user,$username)) {
$user = "$user,1";
$q = "INSERT INTO users VALUES ('$username')";
}

if ($result($user[0-9*])) {

mysql_bottom

mysql_findup REGEXP '[0-9*]'

$i=mysql_copy
$i++;
$user = "$user,$i";
$q = "INSERT INTO users VALUES ('$username')";
}

Regards, David.

[hairpuller]

Can anybody else comment as to whether the above is possible, or do I have only the option of searching for every possible numeral?

[tekky]

Something like this would work, it grabs all users like abc%... so
abc1
abc2
abc10

then loops through the results removing 'abc' and comparing the numerical value only...
so you get... 1, then 2, old_n (1) +1 == 2 so it keeps going...
10, old_n (2) + 1 != 10, so it returns 'abc' . 2+1 'abc3'

<?php
$username = 'abc';
$query = "SELECT username FROM users WHERE username LIKE '$username%';"
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {
   $n = str_replace($username, '', $row['username');
   if ($old_n+1!=$n) {
      $new_user = $username . ($old_n+1);
      break;
   }
}
?>

[Weedpacket]

I thought I'd play with the idea, too. Given the username root in $username ("abc" in this case) and an array $taken of all usernames that have that root followed by a positive integer, I decided to see what I could get these array functions to do.

$numerics = str_replace($username,"",$taken);
$count = count($numerics)+1;
$numerics = array_intersect($numerics, range(1, $count));
$available = array_diff(range(1, max($numerics)), $numerics);

$first_free = empty($available) ? $count : min($available);

echo $first_free;

The array_intersect line is VERY IMPORTANT if "abc987868767" is a possibility!

[hairpuller]

Thanks Karl & Weedpacket for your responses. I have only glanced at both your codings. I can follow Karl's but as I'm only a newbie Weedpacket's array coding is way out of my understanding. One aspect I do want to achieve is once an applicant has been given a username with number then that will not be given to any body else, in other words if the person who is given abc3 (which is the last in the list) decides to delete or change his/her username that old username will never be given to another person. I think the only way I can achieve that is to have a separate table or maybe a separate database that records usernames only and a primary table with each persons current username and all other details.

It appears as if Weedpacket's coding won't achieve that so I'll work on Karl's and get back to you when I succeed.

Karl, can you advise me of a site where I could have learned what you have taught me or must I keep asking these type of questions?

Regards, David.

[hairpuller]

Hi Karl, I have tried your code and all I get is a blank screen. I've put my old coding back and it works OK, so it appears as if there is a small error in your code. I have studied it and it appears as if in the 3rd row the ":" should be at the end and not 2nd last. Is that correct? Also in the 6th row there appears to be a missing "]". Is that correct? The coding I am replacing starts with "function", and I wonder if your coding should start with the same. Also in the coding I'm replacing there is "global $conn;" and I wonder if your coding should contain the same.

I have also replaced "$username = 'abc';"
with"$username = '$_POST['user']';"

And I created a 2nd table within MySQL to store the usernames only.

I have tried all those options (with and without) and several others but still cannot get it to work.

Can you, or anybody else, help me please?

Regards, David.

[hairpuller]

I've now managed to get past the blank screen. I found the blank screen was caused by the ";" not being at the end of the 3rd line, the missing "]" in the 6th line and also the single quotes I had surrounding the $_POST['user']

BUT

no numbers appear and also the 2nd table I created for the usernames does not get the entry. I think the later should be solved after I get the numbers appearing.

Can anybody help.

Regards, David.

[hairpuller]

Can anybody else help me here? I am playing around in the dark so any sites availabe to advance some light on this subject would be much appreciated.