Page 1 of 1

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

Posted: Mon Mar 01, 2010 9:29 am
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

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

Posted: Mon Mar 01, 2010 9:42 am
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');

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

Posted: Mon Mar 01, 2010 2:14 pm
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