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

Solution 1:

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.

Solution 2:

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

Solution 3:

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.

Solution 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..

:)

Solution 5:

1. Generate random number.

2. Check if random number is in database.

3. If not, stop, use this number.

4. Go to step 1.