CODEWITHSUNDEEP
phpmysqlidentifier
marc_durain
marc_durain

Reputation: 11

Create an ID based on integer with letter

I have a database containing messages with an "id" column in integer auto increment. Classical. But I would like to create a "visual" id based on this integer id. This is what I want :

A0001
A0002
[...]
A9999
B0001
B0002
[etc]

The ideal would be to generate automatically (in MYSQL) this messageId based on the integer id. I can also generate this id in PHP. But how ? The difficulty is that we have to check the last id in database to calculate what letter prefix the visual id.

Can you help me to generate this ? In MYSQL if it possible directly or in PHP?

Upvotes: 1

Views: 487

Answers (4)

sameer kumar
sameer kumar

Reputation: 149

try this it may help you.

for ($i = 'A'; $i != 'AA'; $i++) 
{
$prefix = $i;
$id = $prefix.sprintf("%03s",$suffix);
for ($j=1;$j<=5;$j++) 
{

$res = "$id"."$j";
echo "$res"."<br>";

}
}

Upvotes: 0

Neil Hampton
Neil Hampton

Reputation: 1883

You could use the following, but it is only good for numbers from 0 to 249999 (A0000 - Z9999)

select concat(char(ord('A')+round(ID / 10000)),ID % 10000) from ...

To convert back from a visual ID you can use the following:

select ord(VISUAL_ID)-ord('A')+mid(VISUAL_ID,2)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can create such ids using a before insert trigger to create the new value.

Basically you would look up the maximum value and then increment it, by doing something like:

(case when maxvalue like '%9999'
      then concat(char(ascii(left(maxvalue, 1) + 1)), '0000')
      else concat(left(maxvalue, 1), lpad(right(maxvalue, 4) + 1), 4, '0')
 end);

However, I would discourage you from doing this and just set up a regular auto-increment column.

Upvotes: 0

Robert
Robert

Reputation: 20286

have you considered using hex? Why not to write OxA001 it's still integer if you do A001 then it's string and no AUTO INCREMENT is possible.

But if you want really to do it in mysql the only way would be using TRIGGERS.

You have 2 options you can create insert AFTER or BEFORE Insert statement and in this trigger you should update the value of ID to your value based on previous row. But I really recomend using hex.

You can read more about triggers here

Upvotes: 0

Related Questions