How to get next alpha-numeric ID based on existing value from MySQL

Question

First, I apologize if this has been asked before - indeed I'm sure it has, but I can't find it/can't work out what to search for to find it.

I need to generate unique quick reference id's, based on a company name. So for example:

Company Name                Reference
Smiths Joinery              smit0001
Smith and Jones Consulting  smit0002
Smithsons Carpets           smit0003

These will all be stored in a varchar column in a MySQL table. The data will be collected, escaped and inserted like 'HTML -> PHP -> MySQL'. The ID's should be in the format depicted above, four letters, then four numerics (initially at least - when I reach smit9999 it will just spill over into 5 digits).

I can deal with generating the 4 letters from the company name, I will simply step through the name until I have collected 4 alpha characters, and strtolower() it - but then I need to get the next available number.

What is the best/easiest way to do this, so that the possibility of duplicates is eliminated?

At the moment I'm thinking:

$fourLetters = 'smit';
$query = "SELECT `company_ref`
            FROM `companies`
          WHERE
            `company_ref` LIKE '$fourLetters%'
          ORDER BY `company_ref` DESC
          LIMIT 1";
$last = mysqli_fetch_assoc(mysqli_query($link, $query));
$newNum = ((int) ltrim(substr($last['company_ref'],4),'0')) + 1;
$newRef = $fourLetters.str_pad($newNum, 4, '0', STR_PAD_LEFT);

But I can see this causing a problem if two users try to enter company names that would result in the same ID at the same time. I will be using a unique index on the column, so it would not result in duplicates in the database, but it will still cause a problem.

Can anyone think of a way to have MySQL work this out for me when I do the insert, rather than calculating it in PHP beforehand?

Note that actual code will be OO and will handle errors etc - I'm just looking for thoughts on whether there is a better way to do this specific task, it's more about the SQL than anything else.

EDIT

I think that @EmmanuelN's suggestion of using a MySQL trigger may be the way to handle this, but:

• I am not good enough with MySQL, particularly triggers, to get this to work, and would like a step-by-step example of creating, adding and using a trigger.
• I am still not sure whether this will will eliminate the possibility of two identical ID's being generated. See what happens if two rows are inserted at the same time that result in the trigger running simultaneously, and produce the same reference? Is there any way to lock the trigger (or a UDF) in such a way that it can only have one concurrent instance?.

Or I would be open to any other suggested approaches to this problem.

Answer 1

If you are using MyISAM, then you can create a compound primary key on a text field + auto increment field. MySQL will handle incrementing the number automatically. They are separate fields, but you can get the same effect.

CREATE TABLE example (
company_name varchar(100),
key_prefix char(4) not null,
key_increment int unsigned auto_increment,
primary key co_key (key_prefix,key_increment)
) ENGINE=MYISAM;

When you do an insert into the table, the key_increment field will increment based on the highest value based on key_prefix. So insert with key_prefix "smit" will start with 1 in key_inrement, key_prefix "jone" will start with 1 in key_inrement, etc.

Pros:

• You don't have to do anything with calculating numbers.

Cons:

• You do have a key split across 2 columns.
• It doesn't work with InnoDB.