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

user_451959 0
Viewed 68785

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.

mysql
12 Answers

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.

user_55102080

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.

user_14708620

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

user_50229180

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
user_115571380

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;
user_45057520
Related