find fist unused id

[curtm]

I have a table with names and ssn's.  I am importing data into this table using php.  Sometimes part of the data is not correct, but I need to import the other info anyway.  For example, instead of using a real ssn, they may have put something like "Not Online".  The table I am importing into requires a proper ssn. 

 

What I want to accomplish is after I verify the FORMAT of the ssn, and find it to be invalid, I want to set the ssn to "000000000" and then check if that ssn EXISTS in the table already (some of the data is duplicate), and if that ssn exists, add one (000000001) and repeat the checking of its existence.  Basically I want to find the next lowest ssn available that doesn't already exist in my table.

 

  I can already check to see if a ssn exists and add one if it does, but I am lost when it comes to getting the script to loop after that until the ssn does not exist.  Any help would be appreciated!

[.josh]

Dunno what you're trying to accomplish overall here, but if the user does not have valid info, I'd probably be wanting to make a script that will prompt them to enter valid info in later, or at least, be able to select columns based on invalid info for my own purposes, later on.  Having said that, I don't think I'd be wanting to assign unique invalid numbers like that.  What's the point? Again, I don't know the bigger picture here, so that's why I'm saying that. 

 

But if you're insisting on assigning unique invalid ssn numbers... you're going to have to have a way to uniquely identify them, anyways.  Let's use smaller numbers for example purposes. Let's say that the following numbers are legitimately imported ssn numbers:

 

1000

1234

1400

1800

 

Now your next entry has some invalid format, so you're wanting to assign the number 0000 to it, right?  And the next invalid one you're wanting to assign 0001 to it, right? And so on and so forth.  So what happens when you get to the one thousandth invalid number?  Will 1001 be the next unique invalid number assignment?  So how are you going to be able to tell which ones are valid and which ones aren't?  If it doesn't matter, then why bother importing the data to begin with? 

 

I think what I would personally do is either assign all invalid formats the same thing (000000000 for example), or else change your column type to varchar or something, and make it to where you do like i0000, i0001, i0002, etc... in the same column as the regular ones.  That way you don't have to interfere with the legit numbers, figure out where the next "gap" is, etc...

 

but again, I don't know what your bigger picture is, just making suggestions based off the given info.  I'm just failing to understand why you would want to assign unique invalid numbers. 

[Psycho]

I'm in complete agreement with Crayon (also because looping queries, such as that, is terribly inefficient and will probably slow to a crawl). By just randomly assigning SSN's to users you risk giving them a SSN that a later user might legitimately need.

 

But, let's assume the "worst case" scenario: You cannot change the DB field properties, it is an int type field, and it is set to be unique.

 

Well, SSNs are in the format xxx-xx-xxxx. According to this page: http://www.ssa.gov/employer/stateweb.htm

the value 000 is not a legitimate value for the first three numbers. So, you could use anything from 0 to 999,999 (000-00-0000 to 000-99-9999) to identify users that did not have a valid SSN. You could also use that information for later when trying to clean up the data or to display "No SSN" for those records within the system.

 

So, if you use that method then you can always find the next available invalid ID like this:

$query = "SELECT ssn FROM table WHERE ssn < 1000000 ORDER BY ssn DESC LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
$last_invalid_ssn = mysql_result($result, 0);
$next_invalid_ssn = $last_invalid_ssn + 1;

 

Of course if you go over 1 million invalid records you would have a problem.