Generating a random id using php for mysql database

Question

I have searched high and low for an answer with no avail.

I am trying to insert a random id number for my users between 9999,99999999. Problem is they have to be unique. I have everything set up but with an auto increment set for the id. I figured I will keep the auto increment field and just add another row for the unique id.

This is what I got so far:

$id = mt_rand(9999,99999999);

    $query = "SELECT * FROM accounts";

    $result = mysql_query($query) or die(mysql_error());

    while($account = mysql_fetch_array($result)){

        if ($id == $account['id']){

    $id = mt_rand(9999,99999999);

    } else {

    $sql = "INSERT INTO accounts (id, username, email, password, sex)
    VALUES ('$id', '$username', '$email', '$password', '$sex')";
    mysql_query($sql);}}

Now I figured if I create a random number, check the database if it exists create another, else insert it, but it is not working, if it a simple solution I apologise.

Thanks again :)

(If 'auto_increment' is used, it can be set to an initial value, if it's just the lower-limit that's of a concern. It won't help if the requirements is a "random ID", but I would suggest keeping the internal ID different from an exposed ID which mostly removes the need [? for obsecurity ?] of a random PK.) — user166390
– user166390, Commented Mar 31, 2011 at 18:11
I am going to have 2 id fields, one called base id, which the member will never know, and there unique id which is what they will use, in my php the base id will be used to update accounts and whatnot as a security measure as accounts will be transferring money. — Beginner PHPer
– Beginner PHPer, Commented Mar 31, 2011 at 18:22
If the purpose is to obfuscate the true primary key in query strings, cookies and sessions, then hashing the ID (ideally with a per-user salt) woud be a better approach. Users themselves should never really need to "use" their integer ID. — cantlin
– cantlin, Commented Mar 31, 2011 at 18:28

Naftali · Accepted Answer · 2011-03-31 18:05:49Z

3

this may seem like a silly answer:

If you are already using auto_increment on a unique key, there is not a need to add another unique key

answered Mar 31, 2011 at 18:05

Naftali

147k41 gold badges247 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

devasia2112 · Accepted Answer · 2011-03-31 18:09:36Z

1

The code that follow is almost 100% sure that your will never repeat, I use myself, never hava any problem.

 function guid(){
    if (function_exists('com_create_guid')){
        return com_create_guid();
    }else{
        mt_srand((double)microtime()*10000);
        $charid = strtoupper(md5(uniqid(rand(), true)));
        $hyphen = chr(45);  // "-"
        $uuid = chr(123)    // "{"
                .substr($charid, 0, 8).$hyphen
                .substr($charid, 8, 4).$hyphen
                .substr($charid,12, 4).$hyphen
                .substr($charid,16, 4).$hyphen
                .substr($charid,20,12)
                .chr(125);  // "}"
        return $uuid;
    }
 }

If you think it is not enough, try to create an prefix before the number, it can be the Year date('Y');

Good Luck

answered Mar 31, 2011 at 18:09

devasia2112

6,0646 gold badges41 silver badges56 bronze badges

6 Comments

user166390 Over a year ago

While a random GUID is [for most practical purposes] guaranteed to "be globally unique", the upper limit here is 99999999 (which is much much smaller than the ~2^127 of a random GUID) and any trivial mapping to this smaller codomain will lead to the Birthday Problem.

user166390 Over a year ago

@Fernando Costa It's still the Birthday Problem :-) The Mersenne PRNG has a very good cycle length but the result codomain is much smaller than the internal domain.

devasia2112 Over a year ago

@pst If you really insist ;) - mersenne twister script --> php.net/manual/en/function.mt-rand.php#99319 after generates the number, before insert, check in your database if the number already exists.

user166390 Over a year ago

@Fernando Costa I can buy that :-)

user166390 Over a year ago

@Fernando Costa "buy that" = "accept" -- the additional check to the DB is required to ensure a true unique value.

|

score 1 · Accepted Answer · 2011-03-31 19:01:42Z

While not a "strong" PRNG (this is actually very weak), one approach is to use a PRNG as described in one-to-one random mapping. This particular solution is shows a PRNG solution for which the cycle length is the range. However, this solution can't be used 'as is' because the domain is different.

See Random-Order Keys -- indirectly from link above -- and the following sections which uses an Additive Congruential Method / Linear Feedback Shift Register PRNG. Here is a table showing the tap positions -- using a 2^23 would allow picking a guaranteed unique number between [9999, 9999 + 2^23-1) or [9999, 8398606). However, other transactional factors, such as "picking an ID but not using it" should be consider.

Obviously this should not be used for ~~true~~ "security" (even with a "true random random", a nonce -- which needs no unique constraint! -- and/or other verification system should be employed and the random IDs are simply an "obscurity" -- these are not security! -- layer.)

Happy coding.

Jess · Accepted Answer · 2011-03-31 18:57:29Z

0

you could just generate a uid, then check to see if it is used.

<?php
  do {
    $uid = rand(9999,99999999);
    $sql = "SELECT * FROM `table` WHERE `uid`=$uid";
    $result = mysql_query($sql);
  while (mysql_num_rows($result) > 0);
    // have $uid

?>

Or because you have the uid, then you could just run an equation on that to generate a uid (like a md5 or sha256 hash or something).

edited Mar 31, 2011 at 18:57

answered Mar 31, 2011 at 18:15

Jess

8,7306 gold badges51 silver badges68 bronze badges

4 Comments

user166390 Over a year ago

Please don't use 'goto' here. I'm not even going to say anything about the SQL string-generation (except this) this time ^^

Jess Over a year ago

Alright, I'll bite @pst, what is wrong with the sql generation?

maxhodges Over a year ago

@pst So is this approach unsound? your comment generates heat but not much light.

user166390 Over a year ago

@MaxHodges See the first revision for the use of goto. And see stackoverflow.com/questions/60174/… or bobby-tables.com for the latter. The fact that it is known to always be a number is irrelevant. Use the proven approach. Teach good habits. Be consistent. Avoid [buggy code] surprises.

Collectives™ on Stack Overflow

Generating a random id using php for mysql database

4 Answers 4

Comments

6 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related