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
– Craig Celeste, Commented May 1, 2015 at 15:03
This might not answer the question, but why generate a random int? Why not just use uuid()? — Mureinik
– Mureinik, Commented May 1, 2015 at 15:04
Does this random username need to be memorable/readable, or is it purely for your own internal use? — Danny Kopping
– Danny Kopping, Commented May 1, 2015 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
– Daniel Li, Commented May 1, 2015 at 15:11
You need to prevent SQL Injection. Please, stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO. — Jay Blanchard
– Jay Blanchard, Commented May 1, 2015 at 15:12

JP. · Accepted Answer · 2015-05-01 15:04:20Z

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 1, 2015 at 15:04

JP.

5,6167 gold badges61 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Li Over a year ago

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.

JP. Over a year ago

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

Daniel Li Over a year ago

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.

Lexi Brush · Accepted Answer · 2015-05-01 16:19:51Z

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 1, 2015 at 16:19

Lexi Brush

6095 silver badges10 bronze badges

4 Comments

Daniel Li Over a year ago

No, I just want an integer as a secret password of sorts when it comes to restoring user data.

Lexi Brush Over a year ago

So the username is the identity column in your table?

Daniel Li Over a year ago

The randomly generated ID as the identity if possible.

Lexi Brush Over a year ago

okay so what you really want is an 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.

Nimantha · Accepted Answer · 2021-09-17 06:44:20Z

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.

Collectives™ on Stack Overflow

How to generate random integer not present in SQL database?

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related