CODEWITHSUNDEEP
phpmysql
rgomez
rgomez

Reputation: 185

Autoincrement Value in PHP

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 which 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.

Upvotes: 0

Views: 1884

Answers (2)

F.Igor
F.Igor

Reputation: 4410

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

Upvotes: 0

Dennis Eichardt
Dennis Eichardt

Reputation: 55

SQL brings that already with it:

Use the SQL-Function LAST_INSERT_ID() similar to the example in the source page I linked you below. Let's assume table foo has an auto-increment field "id":

INSERT INTO 
    foo (auto, text, somevalue)
VALUES
    (NULL,'text', 2);         # id was generated hoer

Then you do:

UPDATE 
    foo
SET 
     somevalue = somevalue + 1
WHERE
     id = LAST_INSERT_ID()

Source: http://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html

Upvotes: 1

Related Questions