Username Increment

[DavidY]

I'm wanting to increment usernames which are to be alphabetic characters 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. I have been provided the following from another forum but I can't get it to work despite correcting in the 3rd row the ":" should be at the end and not 2nd last and in the 6th row there appears to be a missing "]". <?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; } } ?> I am using the login script found in http://evolt.org/article/comment/17/60265/index.html and modified both the above and the script on evolt to the following;$username = $_POST['user']; $q = "SELECT username FROM users WHERE username REGEXP '$username\[0-9*\]'"; $result = mysql_query($q, $conn); if (!$result) { $endusername = $username . 1; }while($row = mysql_fetch_assoc($result)) { $n = str_replace($username, '', $row['username']); if ($n+1!=$n) { $endusername = $username . ($n+1); break; } } function addNewUser($endusername, $password){ global $conn; $q = "INSERT INTO users VALUES ('$endusername', '$password')"; return mysql_query($q,$conn);}But I still cannot get it to work. I am playing around in the dark and would be delighted if I could gain a better understanding of PHP. Can anybody please help?Regards, David.

[justsomeguy]

First, there's a syntax error on this line:$n = str_replace($username, '', $row['username');There's a missing ]. Second, I'm a little confused about what this code is doing:

$q = "SELECT username FROM users WHERE username REGEXP '$username\[0-9*\]'";$result = mysql_query($q, $conn); if (!$result) {  $endusername = $username . 1;}while($row = mysql_fetch_assoc($result)) {  $n = str_replace($username, '', $row['username']);  if ($n+1!=$n) {	$endusername = $username . ($n+1);	break;  }}

If the query doesn't return any results, which means no usernames matched the pattern, then you add 1 to the end of the username. So all usernames will have a 1 at the end of them, even if it already ends in a number. If they entered user1983928 then you rename them to user19839281. Then, in the while loop, you set $n to be the part of the username in the database minus the original username (this time without the 1). Your next check is:if ($n+1!=$n) {That will never evaluate to false. There is no situation where $n+1 will ever equal $n, it's not mathematically possible, so that condition will always evaluate to true, $endusername will be set to $username plus the first numeric value that it found plus one. So if the user enters "user", and the database has a "user1" in it already, no matter if there is a "user2" or "user3" or whatever, the $endusername will always be set to "user2" because of this:

if ($n+1!=$n) {  $endusername = $username . ($n+1);  break;}

It always sets the username equal to the first number it finds plus one. It always stays with the first one because the if statement is always true and the break statement stops the while loop.In order to set this up you will need to keep track of a current "counter" and keep looking at usernames until the counter reaches a value that doesn't have a matching username. So first you would just check if the exact username is in the database, if it's not then you don't need to do anything. If it is then you get the list of usernames that start with what they entered and end with a number, like you're doing, and start the counter at 1. Each time through the loop you increment the counter, add the number to the username, and check if the username equals the value from the database. If not then you've found the first numeric username that isn't taken. So the loop part would look something like this:

$nr = 1;while ($row = mysql_fetch_assoc($result)){  $endusername = $username . $nr;  if ($row['username'] != $endusername)	break;  else	$nr++;}

[DavidY]

First, there's a syntax error on this line:$n = str_replace($username, '', $row['username');There's a missing ]. Second, I'm a little confused about what this code is doing:

$q = "SELECT username FROM users WHERE username REGEXP '$username\[0-9*\]'";$result = mysql_query($q, $conn); if (!$result) {  $endusername = $username . 1;}while($row = mysql_fetch_assoc($result)) {  $n = str_replace($username, '', $row['username']);  if ($n+1!=$n) {	$endusername = $username . ($n+1);	break;  }}

If the query doesn't return any results, which means no usernames matched the pattern, then you add 1 to the end of the username. So all usernames will have a 1 at the end of them, even if it already ends in a number. If they entered user1983928 then you rename them to user19839281. Then, in the while loop, you set $n to be the part of the username in the database minus the original username (this time without the 1). Your next check is:if ($n+1!=$n) {That will never evaluate to false. There is no situation where $n+1 will ever equal $n, it's not mathematically possible, so that condition will always evaluate to true, $endusername will be set to $username plus the first numeric value that it found plus one. So if the user enters "user", and the database has a "user1" in it already, no matter if there is a "user2" or "user3" or whatever, the $endusername will always be set to "user2" because of this:

if ($n+1!=$n) {  $endusername = $username . ($n+1);  break;}

It always sets the username equal to the first number it finds plus one. It always stays with the first one because the if statement is always true and the break statement stops the while loop.In order to set this up you will need to keep track of a current "counter" and keep looking at usernames until the counter reaches a value that doesn't have a matching username. So first you would just check if the exact username is in the database, if it's not then you don't need to do anything. If it is then you get the list of usernames that start with what they entered and end with a number, like you're doing, and start the counter at 1. Each time through the loop you increment the counter, add the number to the username, and check if the username equals the value from the database. If not then you've found the first numeric username that isn't taken. So the loop part would look something like this:

$nr = 1;while ($row = mysql_fetch_assoc($result)){  $endusername = $username . $nr;  if ($row['username'] != $endusername)	break;  else	$nr++;}

[DavidY]

Thanks justsomeguy, I'm still having difficulty and after many attempts I've tried going back to the basics. You say "So first you would just check if the exact username is in the database, if it's not then you don't need to do anything." The piece of code I'm playing with is as follows;function usernameTaken($username){ global $conn; if(!get_magic_quotes_gpc()){ $username = addslashes($username); } $q = "select username from users where username = '$username'"; $result = mysql_query($q,$conn); return (mysql_numrows($result) > 0);}/** * Inserts the given (username, password) pair * into the database. Returns true on success, * false otherwise. */function addNewUser($username, $password){ global $conn; $q = "INSERT INTO users VALUES ('$username', '$password')"; return mysql_query($q,$conn);}Down further is; /* Check if username is already in use */ if(usernameTaken($_POST['user'])){ $use = $_POST['user']; die("Sorry, the username: <strong>$use</strong> is already taken, please pick another one."); }I guess the usernameTaken line is how the ($_POST['user']) connects to $username. What I can't work out is how to get the figure 1 added to the end of "$username", I've tried $username.1 & 'user.1' but neither work.Once I get the 1 added I guess the code that you supplied above is then used but I have little idea at this point just where it will fit in. Your further guidence would be much appreciated.Regards, David.

[justsomeguy]

You can add 1 to the end of $username like this:$newusername = $username . 1;