Generate a Unique ID using PHP and MYSQL

Question

Hi I am creating a system that processes and ID and a UID, The UID we are generating randomly but I am a little stuck, I need to always generate a UID that does not currently exist in the db as the field is a unique field used on the front end so as not to expose the real ID.

So to recap, I am trying to generate a unique id that does not currently exist in the DB the part I haven't got working is the cross checking in the db so it sometimes will give a number that already exists in the db even though it shouldn't thanks in advance.

This is my code so far:

function uniqueID($table) 
{
$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

$possible = '1234567890';
$code = '';
$characters = mt_rand(7,14);
$i = 0;

while($i < $characters) 
{ 

    $code .= substr($possible, mt_rand(0, strlen($possible)-1), 1);
    $i++;

}

$result = $db->query('
SELECT uniqueID
FROM '.$table.'
WHERE uniqueID = "'.$code.'"
LIMIT 1
');

$totalRows = $result->num_rows;

if(!$result)
{

    return $db->error;

}
else
{

    if($totalRows > 0)
    {

        return uniqueID($table);

    }
    else
    {

        return $code;

    }

}

}

What is your method to find out that the code already exists in the database? — Leif, Mar 12 '12 at 13:56
@Leif You can see the method in the code above in the lower part Gabriel, $totalRows is an int, it will be whatever the total amount of rows is from our query. — André Figueira, Mar 12 '12 at 13:59
@AndréFigueira add `echo('RERUN');` inside `if($totalRows > 0)` and `echo('RETURN');` inside of `if($totalRows > 0)`'s else. Verify if you get two echos and where the script have entered. — Gabriel Santos, Mar 12 '12 at 14:08

score 3 · Answer 1 · answered Mar 12 '12 at 13:46

3

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

answered Mar 12 '12 at 13:46

Teson

6,644
8
46
69

Hi, sorry the UID we are creating is made up of numbers only so that doesn't help much... – André Figueira Mar 12 '12 at 13:54
Then you have a problem. `UUID()` is a proven function that will always return a unique string (actually, it's a number but..). If you are generating random numbers in order to "hide" the auto_increment-ed integers - you're doing it wrong. Use `UUID` or at least PHP's `uniqid` and change your unique column to varchar. – N.B. Mar 12 '12 at 15:30

score 3 · Answer 2 · answered Mar 12 '12 at 14:56

To generate unic UID you can use time, i think it was a very small chanse that records will be added in the same second, with two random data.

write some function which return it to you like that

function generate_uid(){
 return md5(mktime()."-".rand()."-".rand());
}

score 3 · Answer 3 · edited Apr 30 '17 at 14:48

3

In PHP there's a function called uniqid()

http://php.net/manual/en/function.uniqid.php

edited Apr 30 '17 at 14:48

Thomas Orlita

1,554
14
28

answered Mar 12 '12 at 15:22

riyuk

174
1
6

score 1 · Answer 4 · answered Mar 12 '12 at 13:56

1

I could talk about generating ids, like the others did, but this is not your question. Your query seems fine. If it returns 0 rows but you seem to find the code in the database, then most likely it only looks the same, but actually isn't. It could be padded by whitespace.

answered Mar 12 '12 at 13:56

Leif

2,143
2
15
26

Thanks, yeah I thought that but I removed the unique key from the field and allowed the duplicates to be added and there was no padding the function was giving up a code which already existed, the only reason i can think why is that it's not working recursively, but I can't see why... – André Figueira Mar 12 '12 at 14:01
From a conceptual point of view I highly recommend adding the unique key again. You don't want duplicate keys there, right? And you have to be able to identify users by it. You will still need your id checking code, but if somewhere in the future you put an error into this code, the unique key violation will alarm you and prevent things from getting **really** bad. – Leif Mar 12 '12 at 14:12
Hi I just removed it to check, obviously will be putting back lol, no worries there - I just needed to see in the DB if the duplicate was being added. – André Figueira Mar 12 '12 at 14:23

Mateo Torres · Answer 5 · 2012-03-12T14:09:00.197

0

One way to solve this is by selecting the last row of your user database and have your script to check for the id field (you can achieve this by performing a select ordering by ID in descendent mode) then you can use that info for randomize numbers greater than that ID.

EDIT

$result = $db->query('
SELECT uniqueID
FROM '.$table.'
');

$already_in_database = array();
while ($row = mysql_fetch_array($result)){
    $already_in_database[] = $row['UID'];    
}

$new = rand(0,$some_max_value);
while(in_array($new,$already_in_database)){
    $new = rand(0,$some_max_value);
}

edited Mar 12 '12 at 14:09

answered Mar 12 '12 at 13:48

Mateo Torres

1,545
1
13
22

The problem with that is we are generating random ID's so this means if we create a really large ID at one time, then the rest of the generated ones will only be larger than that one and we lose a lot of IDs – André Figueira Mar 12 '12 at 13:54
then you could select all UID from your database into an array and have a loop that generates random numbers and check against that array (code in updated answer – Mateo Torres Mar 12 '12 at 14:09
Thanks, Yeah I could do that, but that just seems way too bulky, as I will be doing this on everywhere that I need a uid field, thing is this table is going to have millions of rows so I need something a bit quicker, may end up just letting MYSQL do the work. – André Figueira Mar 12 '12 at 14:14

score 0 · Accepted Answer · answered Mar 16 '12 at 14:55

I figured out what the problem was already, As I mentioned to everyone the code generation was not the issue! The issue was that the cross check was not working correctly. So all I did was removed this loop

while($i < $characters) 
{ 

    $code .= substr($possible, mt_rand(0, strlen($possible)-1), 1);
    $i++;

}

As this was causing my unique ID to end up wrong.

Generate a Unique ID using PHP and MYSQL

6 Answers6