PHP loop and random string generation

[wiggst3r]

Hi

 

I have a database with over 10000 rows in it.

There is a field in the database called pin_number, which I want to assign a unique pin code to.

The pin code must be 7 chars long.

What I'm looking to do, is generate a unique pin for each row of the database in a loop. I'm looking to do this 100 rows at a time.

 

Can anyone help me or put me in the right direction?

 

Thank you.

[GingerRobot]

Doing this in a loop in PHP would be horribly inefficient. Is it mission critical that the pins are completely unique? Do you have any other unique data for the row? A username perhaps? If so, you could take a substring of a salted MD5 hash of the username.

[wiggst3r]

Yes, it is mission critical that the pins are unique as there is a function which checks to make sure it isn't already in the database.

I'm not fussed about efficiency right now. I have until monday to assign a unique pin to each users row. Each row, has id, name(fullname), mobile number etc.

How could I do the salted md5 hash of the username and loop through?

 

Thanks

[wiggst3r]

Ok, I have the following code:

 

	$unique_pin ="";
for ($digit = 0; $digit < 7; $digit++) 
{

    $r = rand(0,1);
    $c = ($r==0)? rand(48,57) : rand(97,122);
    $unique_pin .= chr($c);
    
}

 

This works fine and generates a unique pin number.

 

Now I would like to loop through each row, and append the uniquely generated pin to each row in a loop.

 

Any ideas?

[PFMaBiSmAd]

Do you want to allow duplicates? Just using that code by itself, for a large set like 10000 will result in many duplicates.

 

To prevent duplicates you can either generate all the values at one time, storing them in an array, with duplicate detection and removal, until you have 10000 values or you can setup the column in the database as a unique index and let the database generate an error when you try to UPDATE the column with an existing value.

[wiggst3r]

I think setting up the column in the database to be unique is an easier option.

I'm now looking to update each row in batches of 1000.

Is there any way of doing this? It counts 1000 rows, updates them all, then goes from 1001 - 2001 and updates these etc?

I'd rather not change my SQL statement each time, but I also do not want to lopp through all 10000 rows straight away as I'm aware of timeout issues etc, especially as the live database has over 200000 records, I'd rather the script just do updates 1000 at a time automatically.

[Andy-H]

$query = mysql_query("SELECT * FROM users WHERE pin = '' ")or die("Error");
$n = 0;
while ($fetch = mysql_fetch_object($query) && $n < 1000){

$pin = rand(1000000,9999999);
$pin = md5($pin);

$q = "SELECT * FROM users WHERE pin = '$pin' LIMIT 1";
$r = mysql_query($q)or die('Error (2)');
$num = mysql_num_rows($r);

if ($num == 0){

   mysql_query("UPDATE users SET pin = '$pin' WHERE username = '$fetch->username' LIMIT 1");

$n++;

}

}

[wiggst3r]

That code above puts several pins into only one row in the db, not a separate pin for each row...