php - Check if username exists, if so, increment by one -

when username inserted database, such as:

• john_smith

i need check if there john_smith present. if so, increment 1 become john_smith_1

so if following usernames exist:

• john_smith
• john_smith_1
• john_smith_2
• john_smith_3
• ....up john_smith_10

i need next john_smith inserted incremented john_smith_11.

so far, have searched , come this:

  $preferredname= "john_smith"          //check duplicate user names         $duplicate= check_for_duplicate_username($preferredname);                                 //if duplicate, increment preferredname              if ($duplicate)          {           $parts = explode("_",$preferredname);            if (isset($parts[1]))           $preferredname = $parts[0]."_".$parts[1]."_".($parts[2]+1);            else $preferredname = $parts[0]."_".$parts[1]."_1";         } 

this, believe work first matching usernames. problem checking database version of name highest number.. sql:

        function check_for_duplicate_username($name) {      // check if username exists     $sql = "select * users user_username=$name";     //then return duplicates    

while may not best solution in grand scheme of things, here answer addresses trying do. following query returns number of users either $name or $name_123 select count(*) users user_username regexp '$name[_0-9]*$'; if value 0 can use $name itself, otherwise can use $name."_". + number returned query.

but many people have mentioned, prly not best idea autoassign usernames (very web 1.0 :p ). i'd recommend email addresses. option use whatever userid social app uses along field identifying social app (if have multiple), , using autoincremented id field unique primary key..