Ok I have hit my next hurdle. I have though of a few ways this could be done but just cannot wrap my head around it. What I would like to do is as I am accepting membership and move the member to the members database they will be given a member number with the following format of XXXX4XXX XXXX4000 and XXXX4001 are a reserved number. The new member should get the next lowest available. As members progress their stages they do end up changing their number making it available again.

So I accept the member it looks for the next lowest number available and are assigned it. I could theoretically have an XXXX4002 XXXX4018 XXXX4006 at any given time. The chance of having more than 50 members with this id format is probably unlikely.

I have though about selecting from the database all ID's that have XXXX4XXXX format and then placing them into an array and doing some sort of comparison or if statement so do this and setting this to my input variable.

5 Years
Discussion Span
Last Post by BrickZ28

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?

Edited by diafol: n/a


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?

Votes + Comments
Thanks for the help

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)

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.

Edited by diafol: n/a


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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.