Choose a number that is not in the table..

I have a table and every row has a different random number in the "Number" column. But how do i choose a new random number that doesn't already exist in the table?

Here's the code i've been trying but can't seem to get to work...

$random = rand(0,21);
$search = mysql_query("SELECT * FROM $usertable WHERE IP = '$IP' AND Number='$random'") or die(mysql_error() );
$num_rows = mysql_num_rows($search);
if($num_rows == 0) //no rows have that number
{
$new_num = $random;
} else{
//chose another random number that isn't in the table
}
}

Please help.. it's driving me crazy!

Thanks,
Aseel

I don't know which RDBMS you are using, but some support nested/sub queries (mysql 4.1. does now too), so you could do something like:

select cols from table where id !in(select id from table where condition)

you get the idea - you use the sub-select statment to popluate the contents of an 'in' clause.


christo

>> I have a table and every row has a different random number in the "Number" column. But how do i choose a new random number that doesn't already exist in the table

If you're trying to use this # for a primary key field, you should know that practically every RDBMS has some way to declare a field as a unique sequential field that is auto filled by the RDBMS itself. For example:
MySQL: AutoIncrement type
Microsoft SQL Server: IDENTITY or UNIQUEIDENTIFIER types
Paradox: AutoIncrement type
MSAccess: AutoIncrement type
Interbase: GEN_ID() function inside an INSERT trigger.

So you see that there's some way for every RDBMS to generate its own unique ID and you don't have to go out of your way to generate your own. All you have to do is declare the column type as autoincrement or whatever and let the RDBMS figure it out for itself.

lol - I should be banned from ever answering another question - totally misunderstood the point in that one.

thanks Scorps,

christo

No problem mate... you can cover for me next time. I have brain farts too, esp. after drinking a nice glass of Glenfiddich, which I propose to try in a few