Page 2 - Last insert id ?!?

I think Rudy already provided you with the solution to your problem, but you might not have understood what that was.

The source of your problem is that you are not optimizing your table structure to perform the task at hand. You mention in your initial post that your table contains these two fields:

id int not_null auto_increment primary key
field_x varchar(20) not null

and that the field 'field_x' is unique. Your problem lies right there. Instead of making the field 'field_x' unique, you should leave it as a regular indexed field, which would contain 'cod' or whatever user code corresponding to the role of the user (could be admin, sys, etc). This field in itself does not have to be unique since many users can have a similar role. But you do need a unique user code constructed from the role and the primary key. This is where you, as the developper, adjust your code to make that happen. You can either retrieve both rows and programmatically generate your user code, kinda like this:


or you can retrieve a unique user code directly from mysql by using the syntax provided by SimonJM:
SELECT CONCAT(field_x,id) as user_code

This way, you do not need to update your row after the initial insert, and won't have to worry about concurrency and stuff like that.

Hope this helps

Jerome