PHP中的自动增量值

Question

What's the best way, or, how would you do to have a field in PHP+SQL formatted as AB00000 where the first inserted should be AB00001 and so on. I have a web page in PHP+SQL to create a form and insert it into a table wich one of the columns is the "reference_nr" and my whole code is already made using the ID (AutoIncrement) so I can't use that for that table.

What I would need is something that would always write the last used maybe to another different table and before INSERT into form's table I would SELECT the last value and increment + 1 and INSERT with the result of that math operation.

• Do I need another table or can I select directly from the form's table?
• Do you think it give me problems if two persons try to INSERT the form at the same time?

Answer 1

If your problem is how to get an autoincrement value for a string column with prefix like AB00000, autoincrement fields are only numeric. If the field have a fixed format you can create a plain insert using the id value from:

SELECT CONCAT('AB', LPAD( MAX(SUBSTRING(id,3)+1 ,5,'0') from table

And use this value directly in your insert to avoid transaction problems