Can I put this in a while loop ?

[jeddiki]

I am wanting to create a unique user id for my
clients, so I am taking the first 3 letters of there
name and adding the 4 digits from the rand() function

If the clients namd is david,
this shoud give me something like:

$Db_user = davi7024

Now I want to check that this IS a unique user id so
I look it up in the database.

Now if the record exists, I want to regenerate the user-id with the rand
again and then re-check. If found, do again and re-check again etc.

Do do the multiple re-checks, I guess that I need to put the
look up in a while loop.

But I am not sure how to use the while loop in this case.

This is what I have so far:

$rand = rand(124,987);
$Db_user = substr($Db_name,0,4).$rand;
 
// Test for unique new contact name.
   $sql = "SELECT * FROM clients WHERE user_id = '$Db_user'";
 
   $result = mysql_query($sql) or die("could not execute FIND MEMBER query");
 
if(mysql_num_rows($result) > 0){
       $rand = rand(124,987);
       $Db_user = substr($Db_name,0,4).$rand;
           exit();
 }  // endif  

Can someone please help me put this in a while
loop so that I ensure unique user_ids ?

Thanks.

[code green]

I would probably do it by SELECT all the current ids and place them in a 1d array.
Then like you say, create a while loop creating random numbers and testing

while(in_array($yourandno,$dbrandnos))

Just a tip when testing using while loops. Always have a get out clause to prevent infinite loops.ie

$c++;
if($c>1000) 
break;

But there may be a more efficient way using purely MySql

[Dormilich]

if you’re fine with plain numbers as user id you can use the AUTO_INCREMENT option for the db field (no need to pass anything at all for user id).

[jeddiki]

Yes I did consider using the auto increment
but felt that as this is a login id, an incremental number
could be easily guessed.

I mean say my user_id is stev078 and I see someone called
Mike also as a new user, I could guess that his user id
will be mike079 or mike080 etc.

Of course there will be a password as well, but I just thought
both should be a bit secure.
So I went for the random number.

Any idea how I put this particular check into a loop ?

[jeddiki]

Hi Code Green
do you mean something like this:

// Test for unique new contact name.
   $sql = "SELECT user_id FROM clients";
 
   $result = mysql_query($sql) or die("could not execute query.". mysql_error());  
 
   while($Db_user is in $result){
       $rand = rand(124,987);
       $Db_user = substr($Db_name,0,4).$rand;
    }  // endif
 

It's this bit that I am not sure of:
$Db_user is in $result

Do I need to use a for loop to step through all the
elements of the result array - or can I do it more simpler ?

[ziycon]

Try something like this:

$rand = rand(124,987);
$Db_user = substr($Db_name,0,4).$rand;
 
// Test for unique new contact name.
$sql = mysql_query("SELECT * FROM clients WHERE user_id = '$Db_user'") or die(mysql_error());
$result = mysql_fetch_array($sql) or die(mysql_error());
while($result) {
    while($Db_user == $result['user_id']) {
        $rand = rand(124,987);
        $Db_user = substr($Db_name,0,4).$rand;
    }
}

[code green]

It's this bit that I am not sure of:
$Db_user is in $result

You're right this won't work.
You will have to loop through the entire result set first to get the ids in a 1d array

// Test for unique new contact name. 
   $sql = "SELECT user_id FROM clients";   
   $result = mysql_query($sql) or die("could not execute query.". mysql_error());   
  while($row = mysql_fetch_assoc($result))}
      $allids[] = $row['user_id'];
   }
   //Initialise while loop
   $rand = rand(124,987); 
   $Db_user = substr($Db_name,0,4).$rand; 
   while(in_array($Db_user,$allids)){ 
       $rand = rand(124,987); 
       $Db_user = substr($Db_name,0,4).$rand; 
    }  // endif 

Actually this should be a Do While Loop but will still work
(Check syntax)

[bilibytes]

I would not bother getting all the userIds from the db i would check if it exists:

//control
$inserted = false;
while($inserted==false){
   //generate the user id
   $userId = ....;
   $resultset = mysql_query('SELECT * FROM users WHERE user_id = $userId');
   if(mysql_num_rows($resultset) == 0){
      //if there is no resultset then it does not exist so make insertion
      mysql_query('INSERT user_id....');
      $inserted = true;//get out of the loop
   }
}

[Dormilich]

@jeddiki
sure, use one of the available DB handling classes (PDO, MySQLi, MDB2).

[code green]

Surely this is inefficient bilibytes.
You are executing a query and two functions, an unknown number of times within a while loop.

That is why I suggested collecting all the data in an array with one query.

It could be modified to

$sql = "SELECT user_id FROM clients";    
   $result = mysql_query($sql) or die("could not execute query.". mysql_error());    
  $unique = false;
while(!$unique){
    $row = mysql_fetch_assoc($result)
    $Db_user = substr($Db_name,0,4).rand(124,987);  
        if( $Db_user !=  $row['user_id'] 
         $unique = true;
 } // endif

[kasmanaft]

вот дурачьё! =)

do {
    $rand = rand(124,987);
    $Db_user = substr($Db_name,0,4).$rand;
 
    // Test for unique new contact name.
    $sql = "SELECT * FROM clients WHERE user_id = '$Db_user'";
 
    $result = mysql_query($sql) or die("could not execute FIND MEMBER query");
} while (mysql_num_rows($result) > 0);

не благодари.. ^^,

[bilibytes]

@code green
This is an interesting point.
I don't know what is better. and it's a good opportunity for me to make light on the subject.

(your solution here is, if im not wrong, not going to work. Because you need one additional while loop. What you are doing now is loop through all the records in the db with mysql_fetch_assoc() regardless of the number of rows returned by mysql_query() and it will throw an exception when you run out of rows (as you are not checking if there is any row left form mysql_fetch_assoc to loop through.) this is the first problem i see.

But then you have another problem: each time you check if the new generated $DB_user exists, you only check it against the row currently being treated by mysql_fetch_assoc(), and not the entire resultset. This will make you insert the new DB_user on the first row that is not the same to your DB_user, which can be pretty early. So you don't solve the problem). You probably meant to do it so:

$sql = "SELECT user_id FROM clients";    
   $result = mysql_query($sql) or die("could not execute query.". mysql_error());    
//create an array with db resultset
while($row = mysql_fetch_assoc($result)){
$resultset[] = $row;
}
//will allow us to use a for loop
$dbNumRows = count($resultset);
$unique = false;
while($notUnique){
    $Db_user = substr($Db_name,0,4).rand(124,987);  
        for($i = 0; $i<$dbNumRows; $i++){
              if( $Db_user ==  $resultset[$i]['user_id']{
                    $notUnique = true;
                     break;
              }//endif
        }//endfor
}// endwhile

[/quote]

Let's break the problem into chunks.
n = the number of times the new DB_user name already exists
m = the number of rows returned by the database

-i call rand() as many times as you do.: so it doesn't count.

-i call mysql_query() n times -1 more than you
-you use a for() loop n times more than i do
-you make a string check n times * m rows
-i call mysql_num_rows() n times more than you

:)) i don't want it to become an 'ego' thing, it really is to understand what is faster. (regardless of the fact that your solution is not possible for a production environement, because the database records may have changed (another user was created) before you finish your array check, resulting in a possibility to have duplicate records)

Please, can any one perform the tests? i dont have a good computer to run the tests on.

Thank you

bilibytes

[code green]

and it will throw an exception when you run out of rows

Well spotted. I missed that. I am too used to using my own database interface class.

But then you have another problem

Yes I see. Did not carefully check my work properly.
I will go back to my un-modified suggestion using in_array(), but the OP did not like my array creation idea

// Test for unique new contact name.  
   $sql = "SELECT user_id FROM clients";    
   $result = mysql_query($sql) or die("could not execute query.". mysql_error());    
  while($row = mysql_fetch_assoc($result))} 
      $allids[] = $row['user_id']; 
   } 
   //Initialise while loop 
   $rand = rand(124,987);  
   $Db_user = substr($Db_name,0,4).$rand;  
   while(in_array($Db_user,$allids)){  
       $rand = rand(124,987);  
       $Db_user = substr($Db_name,0,4).$rand;  
    }  // endif 

Pretty sure there is not a lot wrong with that.
But I believe this to be more efficient because there is only ONE database call.

[Markus]

No.

Use an auto-increment ID in your database table, and then use that number coupled with whatever prefix you want to give it. Simple, huh?

[jeddiki]

Thanks for all your input !

I solved it yesterday
with this:

$rand = rand(124,987);
$Db_user = substr($Db_name,0,3).$rand;
 
// Test for unique new contact name.
$sql = "SELECT user_id FROM clients WHERE user_id = '$Db_user'";
$result = mysql_query($sql) or die(mysql_error());
 
//If member already exists then we are going to keep creating a testing new usernames
while(mysql_num_rows($result) > 0){
    //Then this member name already exists so try another random number
    $rand = rand(124,987);
    $Db_user = substr($Db_name,0,3).$rand;
    $sql = "SELECT user_id FROM clients WHERE user_id = '$Db_user'";
    $result = mysql_query($sql) or die(mysql_error());
}
 
$Db_user = strtolower($Db_user);
 

It works just great :)

I have my random numeric ( better than a sequential one )
and the first letters of the name - easy to remember.

If you see any problems with it, please let me know, but it seems
to be working ok :)

Thanks again

[Markus]

What if the query inside the while loop also is a duplicate?

[whisper]auto-increment - that's what it's for[/whisper]

[bilibytes]

"[WHISPER]" :)
-> [CRY] auto increment is the only good/efficient solution!! [/CRY]
maybe you will hear it now Jeddiki ;)

Your solution is not an efficient solution.

if you really love rand() function as i can see, you can pair it with auto_increment.
Your table would have an additional field.

     id         |        user_id                                                | user_name..
auto_increment  |  substr(user_name, 0, 4) . rand(123,987) . auto_increment    | user name

with this you don't need to check for duplication because autoincrement is unique

but again auto_increment alone is sufficient.

ps: your code can be cut down into this:

$needToCreateUserName = true;
while ($needToCreateUserName){
    $rand = rand(124,987);
    $Db_user = substr($Db_name,0,3).$rand;
    // Test for unique new contact name.
    $sql = "SELECT user_id FROM clients WHERE user_id = '$Db_user'";
    $result = mysql_query($sql) or die(mysql_error());
    //if there are no rows, mysql_num_rows returns 0 wich is false otherwise any number is true
    $needToCreateUserName = (bool) myqsl_num_rows($result);
}
$Db_user = strtolower($Db_user);

bilibytes

[Markus]

@bilibytes
I'm sayin'! :D