How do you generate a non repeating random number in php?

1. Home
2. Mysql
3. How To Generate Random Number Without Repeat In Database Using Php

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

Tags: mysql Answers: 1 | Viewed 66,388 times

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.

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:

1. Selects random number between 0 - 1 using RAND().


2. Amplifies that to be a number between 0 - 99999.


3. Only chooses those that do not already exist in table.


4. Returns only 1 result.

* The answers/resolutions are collected from stackoverflow, are licensed under CC BY-SA 3.0

Some Code Answers

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

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 

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 

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

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

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 

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 ;

call GenerateUniqueValue('tablename', 'columnName') 

$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);

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 

Please leave your answer here:

How do you generate a random number without repeating it?

How do I generate a random number in PHP?

How can I generate 5 random numbers in PHP?

How do I generate a random 4 digit number in PHP?