How to Generate Random number without repeat in database using PHP?

Question

I would like to generate a 5 digit number which do not repeat inside the database. Say I have a table named numbers_mst with field named my_number.

I want to generate the number the way that it do not repeat in this my_number field. And preceding zeros are allowed in this. So numbers like 00001 are allowed. Another thing is it should be between 00001 to 99999. How can I do that?

One thing I can guess here is I may have to create a recursive function to check number into table and generate.

If preceding zeros are allowed, are they compulsory? Is 01 different to 001? — ChrisW, Jul 27 '12 at 00:43
i need 5 digit number, or u can say 5 digit string only. So there is no chance for 01 or 001 to come :) — aslamdoctor, Jul 27 '12 at 00:51
that is not going to happen, that is why I chose the limit of 99999 :) even if reach 9999, that would be too good amount of records for me — aslamdoctor, Jul 27 '12 at 02:29
@aslamdoctor the answer you accepted in this question is incorrect because in the where clause it tries to match the string "random_num" to a list of numbers. You should really unaccept and remove any positive comment from the answer. — Shadow, Feb 17 '18 at 22:04

score 61 · Accepted Answer · answered Jul 27 '12 at 00:47

61

SELECT FLOOR(RAND() * 99999) AS random_num
FROM numbers_mst 
WHERE "random_num" NOT IN (SELECT my_number FROM numbers_mst)
LIMIT 1

What this does:

Selects random number between 0 - 1 using RAND().
Amplifies that to be a number between 0 - 99999.
Only chooses those that do not already exist in table.
Returns only 1 result.

answered Jul 27 '12 at 00:47

Tushar

8,019
31
38

2

This does not look like valid sql. Is it? – iWantSimpleLife Jul 27 '12 at 00:53
3

This is valid and a genius answer. I m waiting to finish the 2mins restriction to accept this answer lolz :) – aslamdoctor Jul 27 '12 at 00:55
1

There's a small chance this will fail if the number table isn't well populated. – Sam Dufel Jul 27 '12 at 00:58
i can now add preceding zeros using php's str_pad function, so whenever any number generated with less than 5 length, that will taken care of. – aslamdoctor Jul 27 '12 at 00:58
6

very nice, potentially slow though as options start to run out, might be faster to preload another table with all possible values and select ordered by rand() from that, then delete the selected record so it can never be selected again. – Kris Sep 04 '12 at 14:48
SELECT FLOOR(RAND()*3 +50) AS random_num FROM tempcode WHERE "random_num" NOT IN (SELECT code FROM tempcode) LIMIT 1 give the values inside the tablle. – manitaz Mar 24 '15 at 04:56
How to return 10000-99999 instead of 0-99999? – zzapper Aug 20 '15 at 11:14
1

SELECT FLOOR(100000 + RAND()* 900000) AS random_num FROM numbers_mst WHERE "random_num" NOT IN (SELECT my_number FROM numbers_mst) LIMIT 1; -- yields a fixed width 6 digit number. – reggoodwin Oct 19 '15 at 12:34
1

For reasons I don't understand, when I do a query like this, mysql ignores the where clause. – Jistanidiot Nov 18 '15 at 14:15
17

This is NOT working like you think it does. in where clause it checks, if the string "random_num" is not in the returned list of the subquery... this is always the case, because my_number is numeric. So please: This is NOT the solution you are looking for ;) – Joshua K Mar 29 '16 at 02:55
15

It is not working, please check this example SELECT FLOOR(RAND() * 9) AS random_num FROM some_table WHERE "random_num" NOT IN (1,2,3,4,5,6,7,9) LIMIT 1 – Purzynski Nov 07 '16 at 12:37
Fixed. Here's a new solution: http://stackoverflow.com/a/40549385/963901 – Ben Guild Nov 11 '16 at 13:49
6

"random_num" is a string not the column name. – Hafez Divandari Nov 10 '17 at 02:27
9

This answer is **incorrect** and should be removed because in the where clause it checks, if the string "random_num" is not in the returned list of the subquery. – Shadow Feb 17 '18 at 22:05
2

not correct answer , "random_num" is a text here and not an alias! and you can't use alias in where clause according to the docs. – user2576266 Mar 11 '18 at 04:11
1

@Jistanidiot The WHERE condition will always be evaluated as TRUE, because the string `"random_num"` will never be equal to any integer value in the `my_number` column. – Paul Spiegel Mar 18 '18 at 19:42
Subquery must check NULL condition like, `SELECT my_number FROM numbers_mst WHERE my_number IS NOT NULL` – Rohan Kumar Aug 24 '18 at 08:58

score 12 · Answer 2 · edited Nov 13 '17 at 07:48

12

In addition to Tushar's answer to make it work when numbers_mst is empty:

SELECT random_num
FROM (
  SELECT FLOOR(RAND() * 99999) AS random_num 
  UNION
  SELECT FLOOR(RAND() * 99999) AS random_num
) AS numbers_mst_plus_1
WHERE `random_num` NOT IN (SELECT my_number FROM numbers_mst)
LIMIT 1

edited Nov 13 '17 at 07:48

Hafez Divandari

8,381
4
46
63

answered Dec 03 '14 at 14:13

Stijn Van Bael

4,830
2
32
38

1

the moment i used the other answer, i discovered this problem. thanks so much for adding your answer to this. super helpful – Katushai Oct 05 '16 at 21:15
This doesn't work in all configurations. Here's a new solution: http://stackoverflow.com/a/40549385/963901 – Ben Guild Nov 11 '16 at 13:49
2

this gives no results in some occassions – Simos Fasouliotis Jan 16 '18 at 22:16

Ben Guild · Answer 3 · 2016-11-11T13:58:25.443

NOTE: The other solutions posted will work only if the column is configured as NOT NULL. If NULL, it'll just return no results. You can fix the query like this:

SELECT random_num
FROM (
  SELECT FLOOR(RAND() * 99999) AS random_num
) AS numbers_mst_plus_1
WHERE random_num NOT IN (SELECT my_number FROM numbers_mst WHERE my_number IS NOT NULL)
LIMIT 1

... The ...WHERE my_number IS NOT NULL is necessary!

EDIT: I just wanted to mention that I intentionally removed the inner SELECT's table name because it didn't seme necessary and seemed to break if there was no data in the table yet? However, maybe this was intentionally included? — Please clarify or comment for everyone, thanks.

Your's works well in being able to actually grab a random number not used. However, try a small data set. If the rand chosen exists it returns nothing. — jimbob, Aug 29 '17 at 09:18

score 4 · Answer 4 · answered Aug 15 '15 at 09:41

4

This is easiest method to build unique code generator without check database, it will save database query execution time.

function unique_code_generator($prefix='',$post_fix='')
    {
        $t=time();
        return ( rand(000,111).$prefix.$t.$post_fix);
    }

Enjoy, have a nice coding day..

:)

answered Aug 15 '15 at 09:41

Ronak Amlani

654
7
15

1

I like this, this is simple, but there's one chance in 111 that it will fail if two users do it at the same time, so you might want to increase the random-number. :) – Niclas Nov 04 '16 at 11:22
Yes, I agree with you. – Ronak Amlani Nov 05 '16 at 05:27
What will happen in case of day light saving issue? – Erfan Ahmed Nov 01 '17 at 08:46
Thanks, for you notice, you can try return ( rand(000,111).$prefix.$t.$post_fix); with return ( rand(000,999).$prefix.$t.$post_fix); – Ronak Amlani Nov 26 '18 at 06:38

score 3 · Answer 5 · answered Jul 27 '12 at 00:46

3

Generate random number.
Check if random number is in database.
If not, stop, use this number.
Go to step 1.

answered Jul 27 '12 at 00:46

David Schwartz

179,497
17
214
278

score 2 · Answer 6 · answered Jul 27 '12 at 00:53

You have two approaches:

The first, suggested in other answer, is to create a random number (using mt_rand() ) and check it's not in the database. If it is in the database, regnerate and try again. This is simplest if you are generating a single number - see other answers for code. But if you are gnerating more that 50% of the numbers it will be very slow and inefficient.

If ou want a lot of numbers, the alternative is to populate a database with all the records and have a column "picked". Run a query to find how many are "not picked" and then find a random number between 0 and the number "not picked". Then run the SQL query to get the number in that position (where not picked, use LIMIT in mysql) and mark as picked. A bit convoluted, it's more work and less efficient if you only want a few numbers, but will be much better when you want to get more that 50% (estimate) of the numbers.

Note: you can make it more efficient by storing the count selected locally and running a few less queries.

You can optimize the second by just deleting the record from the table. So prepopulate a table with sequential numbers, then simply delete them when you pick them randomly. — Lie Ryan, Nov 24 '12 at 11:33
That's true. Often, though, I find I want a record of which number was picked for which user, or which campaign, or on what date, or something like that. (I strongly believe in keeping logs / tracability / tracking of everything, and this means rarely deleting anything, but often adding instead. For random draws (where I live anyway) it's a legal requirement. Depends on the job.) — Robbie, Nov 24 '12 at 11:40

Jafar Pager Jaya · Answer 7 · 2021-07-30T14:12:39.060

2

I know my answer is late, but if anyone is looking for this subject in the future, who want to have a random number with leading zero, you should add the LPAD() function.

So your query it will like

SELECT LPAD(FLOOR(RAND()*99999),5,0)

Have a nice day.

edited Jul 30 '21 at 14:12

answered Nov 13 '17 at 16:57

Jafar Pager Jaya

45
7

Jerem · Answer 8 · 2017-11-24T14:20:41.603

The folowing query generates all int from 0 to 99,999, find values which are not used in the target table and output one of these free number randomly :

SELECT random_num FROM (
    select a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) + (10000 * e.a) as random_num
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as e
) q
WHERE random_num NOT IN(SELECT my_number FROM numbers_mst)
ORDER BY RAND() LIMIT 1

Ok, it is long, slow and not scalable but it works as a standalone query! You can add a remove more "0" (Joins a, b, c, d, e) to increase or reduce the range.

You can also use this kind of rows generator technique to create rows with all dates for example.

score 0 · Answer 9 · answered Apr 12 '18 at 15:44

DELIMITER $$

USE `temp` $$

DROP PROCEDURE IF EXISTS `GenerateUniqueValue`$$

CREATE PROCEDURE `GenerateUniqueValue`(IN tableName VARCHAR(255),IN columnName VARCHAR(255)) 
BEGIN
    DECLARE uniqueValue VARCHAR(8) DEFAULT "";
    DECLARE newUniqueValue VARCHAR(8) DEFAULT "";
    WHILE LENGTH(uniqueValue) = 0 DO
        SELECT CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1)
                ) INTO @newUniqueValue;
        SET @rcount = -1;
        SET @query=CONCAT('SELECT COUNT(*) INTO @rcount FROM  ',tableName,' WHERE ',columnName,'  like ''',newUniqueValue,'''');
        PREPARE stmt FROM  @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    IF @rcount = 0 THEN
            SET uniqueValue = @newUniqueValue ;
        END IF ;
    END WHILE ;
    SELECT uniqueValue;
    END$$

DELIMITER ;

Use this stored procedure and you can use this with dynamic values like

call GenerateUniqueValue('tablename', 'columnName')

score 0 · Answer 10 · edited Aug 27 '18 at 21:53

We can simply do with this:

$regenerateNumber = true;

do {
    $regNum      = rand(2200000, 2299999);
    $checkRegNum = "SELECT * FROM teachers WHERE teacherRegNum = '$regNum'";
    $result      = mysqli_query($connection, $checkRegNum);

    if (mysqli_num_rows($result) == 0) {
        $regenerateNumber = false;
    }
} while ($regenerateNumber);

$regNum will have the value which is not present in the database

score 0 · Answer 11 · edited Aug 18 '19 at 08:13

Very simple, I did the code in MySQL in that stored procedure

It generates the random number of 8 digits and also uniquely with the table in database.

It works for me.

CREATE DEFINER=`pro`@`%` PROCEDURE `get_rand`()
BEGIN
DECLARE regenerateNumber BOOLEAN default true;
declare regNum int;
declare cn varchar(255);
repeat
SET regNum      := FLOOR(RAND()*90000000+10000000);
SET cn =(SELECT count(*) FROM stock WHERE id = regNum);
select regNum;
if cn=0
then
SET regenerateNumber = false;
end if;
UNTIL regenerateNumber=false
end repeat;
END

Brahma K · Answer 12 · 2022-07-21T06:55:23.520

This is an obvious solution but I doubt it can be solved in SQL way. We have to check and regenerate every time it failed to be unique. so, the number of tries are not deterministic. Glad if somebody can prove it's wrong.

declare o int;
select id from otp where chatid=chat into o;
if o is null then
    SELECT FLOOR(RAND()*9000 + 1000) into o;

    while o in (select id from otp) do
        SELECT FLOOR(RAND()*9000 + 1000) into o;
    end while;
    
    insert into otp (id,chatid) values (o,chat);

end if;
return o;

How to Generate Random number without repeat in database using PHP?

12 Answers12

Linked