How do I put extra number at the end of username if duplicate

Hard to explain, and let me show an example.

1. If username foo already exists in MySQL I want php script will allow it but to be foo1
2. Then if foo1 exists too, script will generate the username will be foo2
3. If foo2 existed then become foo3开发者_如何转开发

How to make like that?

Like Col. Shrapnel said Natural increment which seems more sensible. just like "New Folder(3)" stuff in Windows

First, lock the table so no other table will write to it at the same time. then do something like this:

$name = 'foo';
$first_name = $name;
$i = 0;
do {
  //Check in the database here
  $exists = exists_in_database($name);
  if($exists) {
    $i++;
    $name = $first_name . $i;
  }
}while($exists);
//save $name

Another method is to select all names in the table starting with "foo" and ending in a number and then finding the largest number. This can be done in SQL.

The first method is better for use cases with only a small risk of collision, since the pattern matching may be slow, but if you have a lot of collisions the latter may be better.

First, check if the desired username exists in the database:

SELECT COUNT(*) AS numRows FROM table 
WHERE SUBSTRING(username, 1, CHAR_LENGTH(desiredUserName)) = desiredUserName

If numRows is 0, you can leave the username as it is. If it is 1 you can just add '1' to the end of the username. It gets a bit more complicated if numRows > 1. You would need to get the current maxmium number which appends that user name:

SELECT CAST(SUBSTRING(username,(CHAR_LENGTH(desiredUserName) + 1)) AS SIGNED) AS maxNum
FROM table
WHERE SUBSTRING(username,1,CHAR_LENGTH(desiredUserName)) = desiredUserName
ORDER BY CAST(SUBSTRING(username,(CHAR_LENGTH(desiredUserName) + 1)) AS SIGNED) DESC
LIMIT 1

Now you just need to add 1 to the returned maxNum and append that to the desired username

First, use a regular expression to parse out the leading alpha part of the requested username, together with any trailing numeric suffix, something like:

^([a-zA-Z_]+)(\d)*$

The first group gives you the stem (let's assign this to a variable called UsernameStem) and the second group gives you the number (let's assign this to UsernameNumber). Beware that the user may have not entered a numeric suffix, so you need to check for this special case and assign 0 to UsernameNumber.

You can then issue a SQL statement on your users table as follows:

SELECT MAX(username) FROM users WHERE username LIKE :UsernameStem || '%' 

This will give you the existing username with the highest numeric suffix for the user's desired name.

You can again use the regex above to parse out this maximum number - this time examine the second group returned by your regex match. You can then add 1 to this number to give the next available username.

Example: the user request "foo21". The regex will parse out "foo" as the UsernameStem and "21" as the username number. Assuming that you have a user in your database with the username "foo44", the SQL query will return you this username. Applying the regex again to this username from your database will enable you to parse out the number "44". You can then convert this to an int, add 1, then concatenate this with your original UsernameStem. This will yield "foo45".

Good luck!

I would use something like this:

// the username
$username = "foo";

// find all users like foo%
$q = mysql_query("SELECT * FROM users WHERE username LIKE '".$username."%' ORDER BY id DESC");

// no users
if (mysql_num_rows($q) == 0) {
    // create $username
}
else {

    $last_num = 0;

    // find all foo users
    while ($row = mysql_fetch_array($q)) {

        // match foo + number
        if (preg_match("/^".preg_quote($username)."+([0-9]+)$/i", $row['username'], $match)) {

            // extract the number part
            $num = (int) $match[1];

            // set the highest number
            if ($num > $last_num) $last_num = $num;
        }
    }

    // append the highest number to the username
    if ($last_num > 0) $username .= $last_num + 1;

    // create $username
}

Try this one (you need to modifiy this to your needs!)

$username_loop = $username = "foo"; // edit: compatibility to usernames n.e. "foo"
$count = 0;
while (){
  $count++;
  $query =  "SELECT username FROM User WHERE username = ".$username_loop;
  $result = mysql_query($query);
  if ($result){
     $username_loop = $username.$count;
  }
  else break;
}

  $getname   = $_POST['getname'];

  $i++;

  $q =  "SELECT username FROM User WHERE username LIKE '".$getname."%'";

  $r = mysql_query($q);

  if(mysql_num_rows($r) > 0){

    while($row = mysql_fetch_object($r)){
      if($row->username == $getname){
        $uname = $getname.$i;
      }else if($row->username == ($getname.$i)){
        $uname = $getname.$i+1;
      }
      $i++;
    }

  }
  else
  {
   $uname = "foo";
  }

    while (exists($username)) {
        if (!is_numeric(substr($username, -1, 1))) {
            $username = "{$username}0";
        }
        $username++;
    }

    return $username;

You get: test, test1, test2 and so on

Some variation of

$number = 1;
$query = "select name from users where username = ".$username.$number;
$res = mysql_query($query);
$num_rows = mysql_num_rows($res);
while ($num_rows) {
 $number ++;
 $query = "select name from users where username = ".$username.$number;
 $res = mysql_query($query);
 $num_rows = mysql_num_rows($res);
}

The above will increment the number until one is found that is not currently in the database.

You will obviously have to edit the values to suit your setup