incrementing alphanumeric unique id

veena

Hi ,
I have a alphanumeric unique id. Is there any way to increment the id by taking the previous id?
For ex: If the id is "x6a3", I need the next generated id to be "x6a4". Is it possible? Or is there any way to generate a unique alphanumeric id keeping the x6a constant and incerementing only the number part?

amchargue

i don't think you could do that automatically with a single field, but you might be able to do some processing before inserting a new record into the db.

what's after x6a9? x6a10, or x6b0? Or?

veena

After x6a9 is x6a10. Right now I have done it using the substr function , splitting the x6a and rest. Taking the interger value after splitting, i,e if $pid="x6a55"

$proposalid = substr($pid[0],3);
 $proposalid++;

I get x6a56.
I wanted to know if there is any easy way out.

Pig

I would seperate each component, and just run them through the logical process.

if the last int is 99, then increment the alpha, and so on. I don't think there is an simple way, however you could just build the function once an call it when you need it. Just make the input to the function be the current max id.

ahundiak

I think that veena is asking if mysql can generate a custom key automatically. Sadly, the answer is no (or least for the current versions of mysql). Other databases will allow you to program the logic directly into the database thus reducing the complexity of the client program.

With mysql you have to implement the logic in php probably using a table to keep track of the last used id. Make sure you declare the key column as being UNIQUE when creating the sql table to preclude getting duplicate values when two or more users happen to insert at the same time.

Pig

That is correct. You cannot program that knowledg directly into your db. However you can still achieve the result pretty easily and do not need to create a seperate table just for ids.

Just create a function that will run a query to find the max id in your table, and then run that number through a series of processes to determine the next id in the sequence. Return the new number.

Then, whenever you do an insert just call the function before hand, and include that in your insert, rahter than letting it autopopulate.

amchargue

Exactly what I had in mind. To answer veena's most recent, no -- there is no easy way out. :p