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.) — , Mar 31 '11 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, Mar 31 '11 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, Mar 31 '11 at 18:28

score 3 · Answer 1 · answered Mar 31 '11 at 18:05

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 '11 at 18:05

Naftali

144,921
39
244
303

score 1 · Answer 2 · answered Mar 31 '11 at 18:09

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 '11 at 18:09

devasia2112

5,844
6
36
56

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](http://en.wikipedia.org/wiki/Birthday_problem). – Mar 31 '11 at 18:14
@Fernando Costa It's still the Birthday Problem :-) The [Mersenne PRNG](http://en.wikipedia.org/wiki/Mersenne_twister) has a very good cycle length but the result codomain is much smaller than the internal domain. – Mar 31 '11 at 18:22
@pst If you really insist ;) - mersenne twister script --> http://www.php.net/manual/en/function.mt-rand.php#99319 after generates the number, before insert, check in your database if the number already exists. – devasia2112 Mar 31 '11 at 18:32
@Fernando Costa I can buy that :-) – Mar 31 '11 at 18:36
@Fernando Costa "buy that" = "accept" -- the additional check to the DB is required to ensure a true unique value. – Mar 31 '11 at 19:02
@pst Good it fits as a solution. I always check documents numbers in my DB. If repeat, deny otherwise accept. Thats All. – devasia2112 Mar 31 '11 at 19:08

score 1 · Answer 3 · 2011-03-31T19:01:42.840

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 · Answer 4 · 2011-03-31T18:57:29.380

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 '11 at 18:57

answered Mar 31 '11 at 18:15

Jess

8,628
6
49
67

Please don't use 'goto' here. I'm not even going to say anything about the SQL string-generation (except this) this time ^^ – Mar 31 '11 at 18:17
Alright, I'll bite @pst, what is wrong with the sql generation? – Jess Mar 31 '11 at 18:58
@pst So is this approach unsound? your comment generates heat but not much light. – ChatGPT Mar 06 '13 at 06:55
1

@MaxHodges See the first revision for the use of `goto`. And see http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php or http://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. – Mar 06 '13 at 06:58

Generating a random id using php for mysql database

4 Answers4