Update new column in existing table with unique values?

we5inelgr

Hi all,

I've got an existing table with 1647 rows. I've added a new column that needs to have unique values in it. The values will be created from Upper and lower case letters, as well as numbers. The script I have to do this one time update is this:

for ($i=0;$i<1672;$i++) {
    //generate a 12 digit random alpha-numeric for session id
    $length=12;
    $pool="";
    // set pool of possible char
    if($pool == ""){
        $pool .= "abcdefghijklmnopqrstuvwxyz";
	 $pool .= "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	 $pool .= "0123456789";
    }// end if
    mt_srand ((double) microtime() * 1000000);
    $id = "";
    for ($index = 0; $index < $length; $index++) {
	 $id .= substr($pool, (mt_rand()%(strlen($pool))), 1);
    }//** END nested for loop for $index **//

echo "ID: " . $id . "<br>";
$sql = mysql_query ("Update Users Set Unique_ID = '$id'") or exit (mysql_error());

When I run this, each user gets updated with the same unique ID generated each time through the loop. In the end, all users wind up with the very last $id generated.

How can I make this Update Unique so that each time through, the next user in the table gets the next unique $id?

Thanks.

bradgrafelman

Is there a specific requirement that states what the pool of characters used must be?

If not, then why use PHP at all? Let MySQL create the random values for you, e.g.:

UPDATE Users SET Unique_ID = LEFT(MD5(UUID()), 12)

Otherwise, the reason every user gets updated with the same value is because that's exactly what you're telling MySQL to do; after the for() loop runs, you're only using the last value in $id to issue a single UPDATE query (one without a WHERE clause, meaning it will be applied to all rows in the table).

EDIT: Also, despite the extremely small possibility of duplicate IDs being generated (especially given the small sample size), I do hope you've still added a UNIQUE index/constraint on that column to ensure the values are indeed unique.