How to generate random integer not present in SQL database?

Question

Currently working on a login script that would allow for multiple users with the same username to exist. The current plan is to generate a random "secret" user id that will take the place of the actual username. So how would I go about generating a random integer and checking to see if has been added?

This is my attempt at the code; however it does not seem to work as it does not seem to do anything.

$looptime = 100;

while ($looptime > 0) {
    $userid = rand(0, 999999999);
    $SQL = "SELECT * FROM Accounts WHERE username = '" . $userid . "'";
    $result_id = @mysql_query($SQL) or die("DATABASE ERROR!");
    $total = mysql_num_rows($result_id);

    if (!$total) {
        $looping = 0;
        //CREATE THE ACCOUNT
        mysql_query("INSERT INTO Accounts (id,username,password, email, money, activated) VALUES ('', '$username','$password', '$email', '0', '1')") or die("REGISTER ERROR!"); //1=account activated

        //CONNECTED
        echo "IMCONNECTED";
    }
    $looptime--;
}

EDIT: The code/number should be fairly easy to remember/jot down somewhere as the user will be able to view it and/or jot it down for account recovery purposes.

why not use an identity column? The database should generate the id for you. — Craig Celeste, May 01 '15 at 15:03
This might not answer the question, but why generate a random int? Why not just use [`uuid()`](https://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_uuid)? — Mureinik, May 01 '15 at 15:04
Does this random username need to be memorable/readable, or is it purely for your own internal use? — Danny Kopping, May 01 '15 at 15:10
The ID the database generates is in order and I would like to have the id randomized to help maintain secrecy of sorts. — Daniel Li, May 01 '15 at 15:11
[You need to prevent SQL Injection.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 01 '15 at 15:12
The ID should be memorable should the user wish to retrieve his/her account. — Daniel Li, May 01 '15 at 15:12
I am quite new to the whole php/sql thingy so please forgive me for using mysql stuff as it was the first (functional) way I got this working. — Daniel Li, May 01 '15 at 15:16
So if I pick a taken username and I get a number to remember, what prevents me to screw up someone with the same username I have? All I need to do is guess the number, which using some simple programming is absolutely trivial. What kind of "forgot password" system is that when I need to provide a number? What happened with sending the reset link to my email that I used to register? — N.B., May 01 '15 at 15:37
Basically the purpose of this is to provide a way for players in a mobile game to retrieve data should they lose their phone/delete the app and wish to continue playing from where they have left off. This means no email registration, no unique username requirement, no password. Just a randomly generated integer and the username they originally created the account with. I don't plan on having automated account retrieval (they will have to send an email) so I do not feel that scripts that guess at passwords will be very effective. — Daniel Li, May 01 '15 at 15:45

score 1 · Accepted Answer · answered May 01 '15 at 15:04

1

I would suggest either using a UUID/GUID (not an integer) to minimize the possibility of clashes, or using an identity column (not random) to guarantee uniqueness. Does it have to be an integer and does it have to be random?

answered May 01 '15 at 15:04

JP.

5,536
7
58
100

It is meant for the user to have a way to retrieve the account if they remember their secret key so it would be preferable to have a series of numbers that they can fairly easily remember. With that said, what is a UUID/GUID? I am quite new to php/SQL in general. – Daniel Li May 01 '15 at 15:09
Typically UUIDs/GUIDs are used in this scenario. You'll often see email links for resetting password that pass such an identifier in the request. This way the user doesn't need to type anything in... – JP. May 01 '15 at 15:11
UUIDs and GUIDs do not seem suitable for my purposes as I just want code that users can jot down in the event that they lose their account. – Daniel Li May 01 '15 at 15:24

score 1 · Answer 2 · answered May 01 '15 at 16:19

1

Are you using an integer for the ID in the table? You could append this ID to the username. For example: MyUsername1234, MyUsername1245.

answered May 01 '15 at 16:19

Lexi Brush

581
4
10

No, I just want an integer as a secret password of sorts when it comes to restoring user data. – Daniel Li May 01 '15 at 16:59
So the username is the identity column in your table? – Lexi Brush May 01 '15 at 18:04
The randomly generated ID as the identity if possible. – Daniel Li May 01 '15 at 18:28
okay so what you really want is an [identity column](http://en.wikipedia.org/wiki/Identity_column) in your table, usually this is an integer, or perhaps a GUID/UUID. In the case of an integer it's not random, but normally counts up from 1. This will allow you to insert multiple rows with the same username, and this will allow you to do what you want, i.e. what I originally suggested. – Lexi Brush May 01 '15 at 21:20

score 0 · Answer 3 · edited Sep 17 '21 at 06:44

Here is a way you could do it. Create a scalar-variable function in your database (similar to below):

CREATE FUNCTION [dbo].[fn_randomNumber] (@guid nvarchar(128)) 
RETURNS int  AS  
BEGIN       
    SET @guid = REPLACE(@guid, '-', '');
    DECLARE @idInt varchar(Max) = '';

    DECLARE @i INT = 0;
    DECLARE @char VARCHAR;

    WHILE(@i < LEN(@guid))
    BEGIN
        SET @char = (SUBSTRING(@guid, @i, 1));

        IF(ISNUMERIC(@char) = 1)
        BEGIN
            SET @idInt = @idInt + @char;

            IF(LEN(@idInt) = 9)
            BEGIN
                BREAK;
            END
        END

        SET @i = @i + 1;
    END

    RETURN CAST(@idInt as INT);
END
GO

Then execute this script:

SELECT [dbo].[fn_randomNumber] (
   newid())

Of course you will want to evaluate the result to make sure it doesn't already exist.

How to generate random integer not present in SQL database?

3 Answers3