# Random uniqueID for each ID SQL

How can I improve this code? I wanted to know if there's a possibility to remove the for loop, and UPDATE a different uniqueID for each ID in my database.

<?php

$reponse =$bdd->prepare("SELECT MAX(ID) FROM tuto WHERE title=''");
$reponse->execute();$IDnow = $reponse->fetch(PDO::FETCH_ASSOC);$IDnow = $IDnow['MAX(ID)']; for ($i=2 ; $i <=$IDnow ; $i++){$reponse = $bdd->prepare("UPDATE tuto SET uniqueID=:uniqueID WHERE uniqueID='' AND title='' AND ID=:i");$uniqueID = $donnees['uniqueID'];$int = rand(0,51);
$a_z = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";$uniqueID = time().$a_z[$int];

$reponse->execute([':uniqueID' =>$uniqueID, 'i' => $i]); } ?>  ## 2 Answers Finally, I used RAND(), for generate an "UniqueID", and we can add some letters after or else : Thanks to NerdyDev and Bart Friederichs for solutions. <?php$reponse = $bdd->prepare("UPDATE tuto SET uniqueID=ROUND(RAND() * :tp) WHERE uniqueID='' AND title=''");$uniqueID = $donnees['uniqueID'];$tp = time();
$reponse->execute([':tp' =>$tp]);

?>

• RAND() generates a float in [0, 1). This means it is incredibly likely to generate collisions once you're dealing with a non-small number of rows. Think about it this way: a float is 32 bits. That means you have around 4 billion unique values, but RAND() only uses from 0 to 1 (though there is actually a higher concentration of float values around 0 than far away from 0 -- but it's still relatively small). Jan 16, 2016 at 4:00
• In this example, :tp is the time, so RAND() * time. We can generates a lot of values, and we can add letters, etc... Jan 16, 2016 at 8:02
• I'll try to post an answer explaining why this is a flawed approach to generate collision-resistant IDs tomorrow, but for the time being, I've slapped together a simulation in C++ here. If you go to "Edit" then click "Ideone It" you can run it a couple of times. I've yet to see it take more than 10,000 ID generations to hit a collision. If these IDs just need to be likely-unique (~ 0.01% chance of collision), then this approach will work, but if you need an almost unimaginable chance of collision, then this is likely to cause you a lot of pain. Jan 16, 2016 at 9:05
• (It might be worth noting that the above simulation makes a few assumptions about MySQL's RAND(). Primarily, the MySQL manual does not specify whether RAND returns a 32 or 64 bit float, so I have assumed 32 bit. Also, I have assumed RAND is internally using Mersenne Twister and very rarely reseeding (two things I consider fair assumptions)). Jan 16, 2016 at 9:08
• Yes, I agree with you, but the collision is not important for a news UID, and we can put a condition to check If there are some collisions and regenerates by using RAND(). Jan 16, 2016 at 9:31

using foreach() loop will resolve your question.

<?php
$reponse =$bdd->prepare("SELECT MAX(ID) FROM tuto WHERE title=''");
$reponse->execute();$IDnow = $reponse->fetch(PDO::FETCH_ASSOC);$IDnow1 = $IDnow['MAX(ID)']; foreach ($IDnow1 as $IDnow){$reponse = $bdd->prepare("UPDATE tuto SET uniqueID=:uniqueID WHERE uniqueID='' AND title='' AND ID=:i");$uniqueID = $donnees['uniqueID'];$int = rand(0,51);
$a_z = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";$uniqueID = time().$a_z[$int];
$reponse->execute([':uniqueID' =>$uniqueID, 'i' => \$IDnow]);
}
?>


let me know if still any issue stands with your question. Good Luck!

• Not working, "Invalid argument", but I'll try foreach. Jan 14, 2016 at 12:02
• yes the foreach will resolve your problem for sure. and sorry i've might have not completed the code properly.
– NerdyDev
Jan 14, 2016 at 12:33