Auto assign a number

This is a very odd this to try and do. This will break all related tables for the member - unless you take care to change all foreign keys. Seems like a nightmare to me.

Could you explain why you wish to do this? Or have I got it wrong? Is this just a number field that has no relationship to other tables and is not used to uniquely identify a member?

Ardav, I would be more than happy to go into detail. So each person is assigned a certain number depending on what unit they belong too. Everyone begins with XXXX and then some sort of number form afterwards and the number part does change. Our current website does this for when they first join. Now I think that I could just make it do numbers and make the XXXX part just a perma fixture in the displayed table that shows this info so that would mean I would only need to select a number between a specific range.

This table doesnt have any impact and is merely a Member ID field. I would even compromise if need be to have it be a random number just not repeat itself. I have been raking my brain for about 15 hours on this now so any help or ideas would not be turned down. Thank You.

SO Your 'IDs' have the following format:

XXXX4XXX , where each X is an integer, but the last XXXX cannot be 4000 or 4001 and there can be no duplicates in all the IDs, e.g. 10964891

Can the ID be just 14078? i.e. without leading zeroes?

How is this data stored in DB? varchar or integer?

IDs will be constantly disallowed and allowed as members take new IDs and give up their old ones.

That right?

To generate a random number with leading zeroes:

$l = mt_rand(0,9999); //change this to (1000,9999) if you must have strict 8 digit integer
$l = str_pad($l,4,'0',STR_PAD_LEFT); //leave out this line if you don't want leading zeroes
$e = mt_rand(2,999);
$e = str_pad($e,3,'0',STR_PAD_LEFT);

echo $l . 4 . $e;

However, you'd need to check for its existence. Your chance of a hit is v.small, but a possibility.

How you'd be able to get the minimum available number is beyond me for now. If an user gets shot of their number an then you do a search for the lowest number available, they may end up with the same number. Or would they?

The first for XXXX would be letters and is a constant same set of letters i.e. USAF4000 or USAF 4018. They are stored as VARCHAR as of now.

Correct in that USAF4000 and USAF4001 would be reserved. As the new member comes in they get USAF4004 next one gets ideally USAF4005 and so on. Lets say that USAF40012 moves on his number should be able to be used again. Typically I would say about 20 of these numbrs are being used at one time and its a constant rotation. The Rand should work but as you pointed out there is a chance of repeat and I could honestly limit it to abou 50 numbers, USAF4003 - USAF4050

Is it possible to SELECT from DB all instances of USAF40%% place it in an array and then compare that to a random number and if qual redo the random number?

GET FROM DB USAF40%% (can you use a wildcard to get all instances of numbers with 4000?)
DB_num = array of numbers from DB(gets all the USAF4000 numbers)
ID = RANDOM # (generates random number)
IF ID= One of the DBNUM (checks to see if the random num is already there)
ID=RANDOM # (if already there creates new number)
INSERT ID in DB

I can omit the USAF for now and make that hard coded HTML so really just focusing on not getting a repeating 4000 number I think may be the easiest.

I think your idea of limiting to an integer is much more sensible. However USAF40012 (40012) does not fit your pattern. I thought you were looking for 4002-4999. Or did you mean 4012?

I'll have a little think about it. OK got it:

"SELECT MIN(t1.num + 1) AS nextID FROM members t1 LEFT JOIN members t2 ON t1.num + 1 = t2.num WHERE t2.num IS NULL"

This will give you the next lowest available number. Swap 'members' for the name of your table and 'num' for the member ID fieldname.

I got the info from here: http://stackoverflow.com/questions/5016907/mysql-find-smallest-unique-id-available.

LOl yea I had a typo. I will give this shot and let ya know. tnx