check the userID is not in use?

[TheStalker]

Hi everyone,

I have a bit of code that creates a a random userID, what i need to be able to do is check the database i have where the userIDs are stored and make sure that that one isnt in use and if it is in use create that another userID and check that one and so on an till it creates on that is not in use.

Im guessing i would need to use some sort of loop? but as i am not that great with loops im unsure what one to use so thought id post here to see if anyone could come with any thing or push me in the right direction?

many thanks:o

[laserlight]

Roughly:

do
    create random user id
    insert user id into table
while insertion fails due to constraint violation

[TheStalker]

so somthing like this?

do
{
$sql = mysql_query("SELECT* FROM user");
$row = mysql_fetch_array($sql);

for ($count = 0; $count < 32; $count++) 
{
	$random = rand(0,2);
	if($random==0)
	{
		$char=rand(65,90);
	}
	else if ($random==1)
	{
		$char=rand(97,122);
	}
	else if ($random==2)
	{
		$char=rand(48,57);
	}
	$userID .= chr($char);	   
}
}
while($row['userID'] == $userID)

[laserlight]

No. The idea is to insert, not select. By selecting to check, there is a short window between the check and the insertion during which a race condition can happen.

[TheStalker]

thanks for the replys and to sorry to sound dumb here, but im not getting what you mean. how can you check to see if someone else is using the userID already? just by inserting?

[laserlight]

Something like this:

do
{
    $userID = '';
    for ($count = 0; $count < 32; $count++)
    {
        $char = 0;
        switch (rand(0, 2))
        {
        case 0:
            $char = rand(65, 90);
            break;
        case 1:
            $char = rand(97, 122);
            break;
        case 2:
            $char = rand(48, 57);
            break;
        }
        $userID .= chr($char);
    }

$sql = sprintf("INSERT INTO user (id) VALUES (&#37;d)", $userID);
}
while (!mysql_query($sql) && mysql_errno() == 1062);

[TheStalker]

so if i do this and there is already an entry in the database that has the userID set as '1111'

it shouldnt insert?

do
{
    $userID = '1111';
    /*for ($count = 0; $count < 32; $count++)
    {
        $char = 0;
        switch (rand(0, 2))
        {
        case 0:
            $char = rand(65, 90);
            break;
        case 1:
            $char = rand(97, 122);
            break;
        case 2:
            $char = rand(48, 57);
            break;
        }
        $userID .= chr($char);
    }*/

$sql = sprintf("INSERT INTO user (id) VALUES (%d)", $userID);
}
while (!mysql_query($sql) && mysql_errno() == 1062);

[NogDog]

Yes, if you have defined that column to either be the primary key or have a unique index in your table definition.

[TheStalker]

NogDog;10912549 wrote:

Yes, if you have defined that column to either be the primary key or have a unique index in your table definition.

ok well its not the primary key or a unique index and i dont really want it to be ?

[NogDog]

Why not? The whole premise of your initial question in this thread would imply that you want the user name to be unique. So why not assign a unique index to it in the database definition and let the database help you to enforce its uniqueness? That's why things like unique indexes were invented.

[bradgrafelman]

And also, why does the userID need to be random? Why not use an AUTO_INCREMENT field?

[TheStalker]

NogDog;10912555 wrote:

Why not? The whole premise of your initial question in this thread would imply that you want the user name to be unique. So why not assign a unique index to it in the database definition and let the database help you to enforce its uniqueness? That's why things like unique indexes were invented.

Ok ill take on board what you are saying and take another look at the database design. Thanks for the replys

[TheStalker]

bradgrafelman;10912560 wrote:

And also, why does the userID need to be random? Why not use an AUTO_INCREMENT field?

its to do with user activation and users not being able to guess IDs easily

[bradgrafelman]

TheStalker wrote:

its to do with user activation and users not being able to guess IDs easily

So, security through obscurity?

I would say a better DB schema would be to leave userID as an AUTO_INCREMENT field and instead add a new field (e.g. "activation_key" or something) and just do a [man]sha1/man on [man]microtime/man (or use [man]uniqid/man).