1

I am developing a mysql database.

I "need" a unique id for each user but it must not auto increment! It is vital it is not auto increment.

So I was thinking of inserting a random number something like mt_rand(5000, 1000000) into my mysql table when a user signs up for my web site to be. This is where I am stuck?!

The id is a unique key on my mysql table specific to each user, as I can not 100% guarantee that inserting mt_rand(5000, 1000000) for the user id will not incoherently clash with another user's id.

Is there a way in which I can use mt_rand(5000, 1000000) and scan the mysql database, and if it returns true that it is unique, then insert it as the user's new ID, upon returning false (somebody already has that id) generate a new id until it becomes unique and then insert it into the mysql database.

I know this is possible I have seen it many times, I have tried with while loops and all sorts, so this place is my last resort.

Thanks

Improve this question
4
  • 3
    use php.net/manual/en/function.uniqid.php Commented Apr 1, 2011 at 0:02
  • Could you explain what is the problem with an auto-incrementing number? Commented Apr 1, 2011 at 0:16
  • "I "need" a unique id for each user but it must not auto increment!" you're making a problem out of something that is not a problem. and you wont get a proper solution. Commented Apr 1, 2011 at 0:39
  • I use the word "need" with heedance, I wouldn't resort to this method if I didn't have to. Commented Apr 1, 2011 at 0:43

6 Answers 6

Reset to default
4

You're better off using this: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

Or using this: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

But if you actually want to do what you are saying, you can just do something like:

$x;
do {
 $x = random_number();
 "SELECT count(*) FROM table WHERE id = $x"
} while (count != 0);
// $x is now a value that's not in the db
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

And then just do a simple update along with all my other $_POST variables using $x as the id? Could you explain the method of it checking how it is not in the db, I haven't seen this method and do not understand it. Thanks.
UUID is the answer (can create using MySQL or a PHP library). The random number approach starts out as a bad idea, but it becomes even worse as you add records and your chance of collision goes up. The whole point in not doing auto_increment is avoiding database contention, and if you start having to do 2, 4 or 20 SELECTS for every INSERT, that's bad.
If you can't have auto number, and the guid is not guaranteed to be unique, this is what I'd do. Either way, I'd let the database handle it. Less round trips back and for to determine if there is a duplicate.
@Carl: Yes you could (although it's a bad approach). What it does is first generates a random number. Then it checks if that number is in the database. If it is, it creates a new number and tries again, and again again until it comes up with something unique.
Thank you. I can see how it is a bad approach now as it could cause massive delays! Is there another option if any? And Thanks again.
0

You could use a guid. That's what I've seen done when you can't use an auto number.

http://php.net/manual/en/function.com-create-guid.php

Improve this answer

1 Comment

If the script stop, the guid doesn't exist anymore right ? So it's not more "link" to the database id.
0

Doesn't this function do what you want (without verification): http://www.php.net/manual/en/function.uniqid.php?

Improve this answer

3 Comments

Yes, that is better than using mt_rand. Thanks. But still my main problem exists any ideas?
uniqid never returns same result twice. Do you mean you already have data in your table?
No but the return of uniqid is 13 characters, I want to try and maintain it to under 8 at best, if I play around with the end result of uniqid there is no guruantee it will be random and still unique.
0

I think you need to approach the problem from a different direction, specifically why a sequence of incrementing numbers is not desired.

If it needs to be an 'opaque' identifier, you can do something like start with a simple incrementing number and then add something around it to make it look like it's not, such as three random numbers on the end. You could go further than that and put some generated letters in front (either random or based on some other algorithm, such as the day of the month they first registered, or which server they hit), then do a simple checksuming algorithm to make another letter for the end. Now someone can't easily guess an ID and you have a way of rejecting one sort of ID before it hits the database. You will need to store the additional data around the ID somewhere, too.

If it needs to be a number that is random and unique, then you need to check the database with the generated ID before you tell the new user. This is where you will run into problems of scale as too small a number space and you will get too many collisions before the check lucks upon an unallocated one. If that is likely, then you will need to divide your ID generation into two parts: the first part is going to be used to find all IDs with that prefix, then you can generate a new one that doesn't exist in the set you got from the DB.

Improve this answer

1 Comment

Thanks for this, this is a totally different approach which I shall look into. How much of a problem is it likely to be if I have random number between say 10,000 and 10mill, and 10,000 users on the database while running a while loop with mdrand? Is it likely to be that bad? I never looked this far into it.
0

Random string generation... letters, numbers, there are 218 340 105 584 896 combinations for 8 chars.

function randr($j = 8){
$string = "";
    for($i=0;$i < $j;$i++){
        srand((double)microtime()*1234567);
        $x = mt_rand(0,2);
        switch($x){
            case 0:$string.= chr(mt_rand(97,122));break;
            case 1:$string.= chr(mt_rand(65,90));break;
            case 2:$string.= chr(mt_rand(48,57));break;
        }
    }
return $string; 
}

Loop...

do{
    $id = randr();
    $sql = mysql_query("SELECT COUNT(0) FROM table WHERE id = '$id'");
    $sql = mysql_fetch_array($sql);
    $count = $sql[0];
}while($count != 0);
Improve this answer

3 Comments

That will suffer from problems of scale: as the table gets more entries, the odds of a collision get higher.
and I can just insert $id as a normal variable along with all my other $_POST variables and what not after the do while? Thanks for your help.
Yes you can, script execution won't continue until unique $id is generated, meaning no IF etc. @staticsan, eventually, yes, but still can be tweaked a little.
0

For starters I always prefer to do all the randomization in php.

function gencode(){
$tempid=mt_rand(5000, 1000000);
$check=mysql_fetch_assoc(mysql_query("SELECT FROM users WHERE id =$tempid",$link));
if($check)gencode();
$reg=mysql_query("INSERT INTO users id VALUES ('$tempid')",$link);
//of course u can check for if $reg then insert successfull
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.