Search code examples
phpmysqlcomposite-primary-keylastinsertid

MySQL+PHP: getting last_id of multiple/composite primary key

I need to get the last inserted id of table that have multi-column primary keys.

  • Those tables does not have AUTOCOUNT column.
  • I'm using parametrized queries (arbitrary order)
  • Using PHP (5.3) and MySQLi module
  • Arbitrary INSERT SQL Query. (In any format)

For example:

Table: foo
Primary key: [ group_id , user_id ]
Query: INSERT INTO foo (group_id, user_id, name, email) VALUES (?, ?, ?, ?);
Parameters: array(34,15,"John","john@example.com")

Result: $last_id = $mysqli->insert_id ?: getInsertedId();

34,15

At this moment I have a function named getPK(), which returns me:

array("group_id","user_id");

What I need now is to implement getInsertedId(). Which could be the easy way to do it without using an SQL parser?

I'm pretty sure there is already an answer for this question but I couldn't find anything....

UPDATE

The reason of why I'm asking this question is because I have a class which control everything related with the MySQL database (part of a personal framework). I have one method that is called set() in which queries (like UPDATE, INSERT, DELETE, etc.) are passed. I have other specific methods like insert() in which arrays are passed.

I have a variable in which I store the last_inserted_id. That variable can be called anytime later. I have many tables in different systems that have multiple-primary-keys. When using the insert() method, I have no problem to set the last_inserted_id value, but when some systems use the set() method, I can not retrieve that value and I have to return 0. I would like to change that behavior.

I wanted to simplify my explanation with the above example.

UPDATE 2

Not all systems are controlled by myself. For example, one of the systems call a soap method in which a query is sent to be executed (any kind of query). Those are handled by set() method. Then there is other soap method in which the last id is retrieved. For consistency I would like to return that value.

Solution

  • As Phil pointed out in a comment, MySQL LAST_INSERT_ID() can only return auto-generated values (AUTO_INCREMENT).

    Furthermore, you can't have an auto_increment on multiple columns: if you have a multiple primary key, then the auto_increment is possible on only 1 column.

    In conclusion, you can't get the key inserted using LAST_INSERT_ID() in your case.

    A solution would be to handle that case in PHP, so that you getInsertedId() method returns the values of primary keys that was given for the insert.