Random Number

NZ_Kiwis

I want my MYSQL DB to make a random 6 digit confirmation number... when someone adds a new row.. how can i do this? has to been unique

NogDog

Random and unique are somewhat contradictory, as a truly random number always has a possibility of being a duplicate. My recommendation for the most efficient thing would be to define a unique column in your table with the auto_increment attribute, and set the first number to 100001 (or whatever you want to start the numbering at).

sneakyimp

i think by 'random' he might have meant 'not predictable' -- like a secret key or something.

maybe try something like this function. NOTE: It won't guarantee that the value it produces is truly unique, but the chances of it producing the same one twice are pretty slim unless you run it quite a few times. if you want to make sure it's absolutely definitely unique, you'll have to keep track of all the values it ever generates and compare it against them

ps: haven't tested this yet

function generate random_alphanumeric_string($length) {
  $chars = array();
  for($i=0; $<10; $i++) {
    $chars[] = strval($i);
  }
  $start = ord('A');
  $end = ord('Z');
  for($i=$start; $i<=$end; $i++) {
    $chars[] = chr($i);
  }

  $last_index = count($chars) - 1;
  $result = '';
  for($i=0; $i<$length; $i++) {
    $rnd = rand(0, $last_index);
    $result .= $chars[$rnd];
  }

  return $result;
}

Hey nog...how many times do you think you could expect to run this before you had a reasonable chance of a duplicate value?

Weedpacket

Then why not just use the last six digits of [man]microtime[/man]?

sneakyimp wrote:
you'll have to keep track of all the values it ever generates and compare it against them

Well, they'll be in the database, won't they? 🙂 Writing a user function in the database to generate the ID would be the way to go in that case. Then it would just be "Insert (...., id, ...)values(..., unique_id(), ...)". It would beat getting all the existing ids out of the database, into PHP, and then generating IDs randomly until a new one is found.

But what I want to know is: what happens when the millionth record is added? Or won't this happen?

bradgrafelman

Weedpacket wrote:
But what I want to know is: what happens when the millionth record is added?

VALUES (..., CONCAT( 'A', SUBSTRING( unique_id(), 0, 5 ) ), ...)

NightShift58

Ah... the purists and their definition of random and unique...

This forum doesn't operate in a vacuum but within the context of PHP, in general, and PHP coding, in particular, and, as such, almost aways just around the corner from MySQL.

Within that real life context, random and unique have a more specific and practial meaning than in some math or statistics book. And I think we ought to keep that way...

Back to the issue of generating a random, unique number... A fairly simple and workable solution - if it fits in your scheme of things - could be to use the autoincrement feature of MySQL, as suggested by NogDog, to generate your primary index for your table. You could then generate a random number within a specified range and concatenate the two to form a numerical value which would be both random enough and unique within your table. This value would then be stored in a separate field in your table.

This could, eventually, result in a random number that will be longer than the six digits you are aiming for.

ssmitra

You could try using md5(), based on the time the row is created, to generate a random number. Store the number in a variable, and then the variable into the database.

$number=substr(md5(time()),0,6);

I hope this bit of code helps. 🙂

sneakyimp

the problem in that case would be if two distinct users were to access the site within the same second because they would get the same value - it wouldn't be unique.

ssmitra

True Sneakyimp, but it's a suggestion. I'm sure there are ways to enforce a unique-ness to the number generated, but I'm too much of a novice to know how- give me a couple of years 🙂

laserlight

I suppose that could be fixed by using microtime() instead of time(), as Weedpacket suggested. However, there is yet another problem with taking the first 6 characters of the hex string of the MD5 hash: the probability for a collision is greatly increased.

6 hex digits gives 24 bits of space. This means that it takes about 1.1774 * 2¹² ~= 4823 rows to have a 0.5 chance of getting a collision. For a 15000 row table, you would be very lucky to have unique confirmation numbers.

EDIT:
then again, I think it is inherently so that a 6 digit confirmation number is likely to produce a collision if it is random for a 15000 row table. The "last 6 digits of microtime()" would be even worse, so it all depends on how many rows are expected for the table in the first place.

sneakyimp

laserlight wrote:
6 hex digits gives 24 bits of space. This means that it takes about 1.1774 * 2¹² ~= 4823 rows to have a 0.5 chance of getting a collision. For a 15000 row table, you would be very lucky to have unique confirmation numbers.

what formula/witchcraft is that? i'd like to know more.

laserlight

what formula/witchcraft is that?

Read up on birthday attacks, and yeah, it does look like witchcraft 🙂

I suppose one could detect a collision and generate a new number, but that may become expensive as the table grows.

sneakyimp

i meant to mention the expensive part of my original suggestion but forgot. for a 6-digit hex number, you get 16⁶ possible different keys which is about 16,777,216 possibilities. if you had 16,777,215 rows in your database, you would have to run my random number generator MANY MANY TIMES before it finally produced that one possibility you didn't yet have in your database.

but seriously laserlight...do i have to read about birthday attacks to find out what formula you are using? got a url?

laserlight

There are 2176782336 possible confirmation codes for your generator. So, a duplicate becomes likely in a 1.1774 * sqrt(2176782336) ~= 54933 row table.

got a url?

I think the Birthday Attack article from Wikipedia will do.

NogDog

If you can deal with a 13-character ID, you could just use:

$id = uniqid('');

The only way you'd get a duplicate is if two requests happen within the same millionth of a second on the server.

sneakyimp

thanks, laser.

Weedpacket

laserlight wrote:
then again, I think it is inherently so that a 6 digit confirmation number is likely to produce a collision if it is random for a 15000 row table. The "last 6 digits of microtime()" would be even worse, so it all depends on how many rows are expected for the table in the first place.

Hence the vital need to check all the numbers that have already been generated, and try repeatedly until one is found (should be feasible to work out how many checks would be needed on average).

Or you could generate all possible ids in a table, and select a random one from that as required, then delete it from the table.

Silly, but still; without knowing why they need to be "random".....

Roger_Ramjet

So you want it to be unique in the db - then you want a quick and efficient fudge, otherwise the collisions will kill you (see laserlight's birthday attack link for details).

Best one I can think of is to generate the numbers in advance and issue them randomly, once issued they can never be reused. OK it needs an extra table but is very efficient after that.

table rand_nos has one column with 100000 to 999999 in it.

$sql = "SELECT rand_no FROM rand_nos LEFT JOIN other_table t USING (rand-no) WHERE t.rand_no=NULL ORDER BY RAND() LIMIT 1";

Will select and return a random no from the series that has not yet been used guaranteeing both unique and random. Use it as a subquery in your insert and you have a very efficient method that requires only 1 query EVER :p