How to set a php loop for a unique username check in MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
benavery
Forum Newbie
Posts: 2
Joined: Mon Mar 01, 2010 9:12 am

How to set a php loop for a unique username check in MySQL

Post by benavery »

G'day all,

First post so be kind!

I have a mysql login system whereby I am going to setup certain "power users" to have permission to add new users to the database. The database is written in MySQL.

To make the task of adding users as simple as poosible, I have only allowed the "power user" to input the forename and surname of the individual. The php page then generates a random 8 char password consisting of a selection of A-Z and a-z. I want the system to automatically generate the username too in the format "first letter of forename" then "full surname". Of course, if you have "John Smith" and "Joe Smith" the username would be the same so I want the 2nd user added to become "jsmith1" and the third to become "jsmith2" and so on.

I am currently using the following query to check if the default username is already in use:

Code: Select all

 
    # Reset user counter
    $au_username_count = 0;
    $au_username_reset = $au_username;
    
    ###################################################
    ## THIS SECTION REALLY NEEDS TO USE A MYSQL LOOP ##
    ###################################################
        
    # Add number 1 to the end of the username
    $au_checkuser_query = mysql_query("SELECT * FROM users WHERE username='$au_username'");
    if(mysql_num_rows($au_checkuser_query) > 0) {
        $au_username = $au_username_reset;
        $au_username_count++;
        $au_username = $au_username.$au_username_count;
    }   
    # Add number 2 to the end of the username
    $au_checkuser_query = mysql_query("SELECT * FROM users WHERE username='$au_username'");
    if(mysql_num_rows($au_checkuser_query) > 0) {
        $au_username = $au_username_reset;
        $au_username_count++;
        $au_username = $au_username.$au_username_count;
    }   
...etc  
 
If I repeat this code several times in a row it will only work that many times, so I'm ideally looking for a way to "loop" this in php until the next available username is generated.

That is what I can't work out how to do!

Help would be greatly appreciated

Ben Avery
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How to set a php loop for a unique username check in MySQL

Post by Eran »

Use recursion:

Code: Select all

function generateUsername($username,$iteration = 0) {
      $generated = $iteration > 0 ? ($username . $iteration) : $username; //Increment username
      $query = "SELECT username FROM users WHERE username='" . mysql_real_escape_string($generated) . "'";
      $result = mysql_query($query);
      if(mysql_num_rows($result) > 0) {
          return generateUsername($username,$iteration + 1);
      }   
      return $generated;
}
 
generateUsername('Joe Smith');
benavery
Forum Newbie
Posts: 2
Joined: Mon Mar 01, 2010 9:12 am

Re: How to set a php loop for a unique username check in MySQL

Post by benavery »

Thank you, that worked fantastically!

New code for those interested:

Code: Select all

 
$au_proposed_username = "jsmith";
 
# Function to check if user exists in database and add next available number to the end if they do
function generateUsername($username,$iteration = 0) {
    $generated = $iteration > 0 ? ($username . $iteration) : $username; //Increment username
    $query = "SELECT * FROM users WHERE username='$generated'";
    $result = mysql_query($query);
    if(mysql_num_rows($result) > 0) {
        return generateUsername($username,$iteration + 1);
    }
    return $generated;
}
 
$au_username = generateUsername($au_proposed_username);
 
Ben Avery
Post Reply