6

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.

lepe
  • 24,677
  • 9
  • 99
  • 108
  • 1
    If you specify `group_id` and `user_id`, why do you want to get them back from the database after the INSERT? – Matthieu Napoli Aug 25 '11 at 09:12
  • While you're working out the code problem, you might want to take another look at that table structure. It appears to be only in 1NF, although I can't tell for sure just by column names and one row of data. – Mike Sherrill 'Cat Recall' Sep 03 '11 at 14:24

2 Answers2

5

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.

Matthieu Napoli
  • 48,448
  • 45
  • 173
  • 261
  • "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." Actually THAT is what this question is about. – lepe Aug 25 '11 at 09:39
  • Please READ AGAIN. I was only saying all that to get to the conclusion that "**you can't get the key inserted using LAST_INSERT_ID() in your case.**". Once that is clear, I propose a solution for you. Cheers – Matthieu Napoli Aug 25 '11 at 09:40
  • Well that's just coding then, not Mysql related (as your question suggested). We don't know you code or anything about its architecture, so here is an answer: **use a PHP variable to store the ids** and then return them using `return array($group_id, $user_id);`... – Matthieu Napoli Aug 25 '11 at 09:41
  • 1
    OK I re-read again your question, if I understood correctly, the **set()** method takes a string SQL query, so you can't get the variable... Well, I'm afraid the problem is with your code architecture. You want to have a class that controls everything, but in reality it doesn't control everything (with **set()**, you don't control the query). – Matthieu Napoli Aug 25 '11 at 09:47
  • Not necessarily has to be fixed in PHP. For example, adding triggers to the tables in order to update the insert_id after INSERT, is a possibility (but not so good). Or maybe MySQL stores somewhere the information I need. The more I think about it the more I get into the conclusion that there is no such information... – lepe Aug 26 '11 at 02:51
0

You can add a Timestamp column that is automatically initialized to the current timestamp when a record is INSERTED and is not updated every time the record is updated:

ALTER TABLE MyTable
  ADD COLUMN InsertTS TIMESTAMP DEFAULT CURRENT_TIMESTAMP
, ADD INDEX InsertTS_ind (InsertTS) ;

You also make sure that no other query, function or trigger changes that value. Then, you can use a query to find the PK of the record with highest timestamp:

SELECT group_id, user_id
FROM MyTable
ORDER BY InsertTS DESC
LIMIT 1 ;

Note: you can do this only if you haven't already a Timestamp in the table that gets auto-initialized and/or auto-updated values.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • (+1) I actually thought exactly about that... but I don't like the idea that there is no way to do it in MySQL. It would be great if after insert/update, you could retrieve the actual inserted/updated values, something like: SELECT LAST_INSERT FROM TABLE; (and return the inserted row(s)). That could be really helpful for me... – lepe Aug 26 '11 at 02:56
  • @lepe: Yes, `LAST_INSERT_ID()` works per connection but only for auto-generated IDs. My answer would work differently - not per connection but through inserts from all connections. There would also be need to care for the case when 2 records have same timestamp. – ypercubeᵀᴹ Aug 26 '11 at 03:14
  • Another possibility is to change the `(group_id, user_id)` key from `PRIMARY` to `UNIQUE` and add an auto-incrementing PK. – ypercubeᵀᴹ Aug 26 '11 at 03:19
  • Then use `SELECT group_id, user_id FROM MyTable WHERE pk = LAST_INSERT_ID()` for your `getInsertedId()` procedure. – ypercubeᵀᴹ Aug 26 '11 at 03:20
  • Thanks ypercube, what I'm looking for is a more general way to do it without having to modify the tables. There is plenty of information processed by mysql (for example: SHOW INNODB STATUS), so I was expecting to obtain that information from MySQL and not directly from the tables. – lepe Aug 26 '11 at 03:48
  • 3
    This is **dangerous**! If you have multiple users and two add a record at a similar time both will end up with the same record. – PiTheNumber Nov 25 '14 at 11:01
  • @PiTheNumber What do you mean? If multiple users want - at the same time - to find the last inserted id, of course they'll get the same values. How else could it be? – ypercubeᵀᴹ Nov 25 '14 at 11:04
  • @PiTheNumber But thnx for pointing this old answer. It seems that what they wanted/needed is a solution in PHP, not MySQL. – ypercubeᵀᴹ Nov 25 '14 at 11:10
  • 1
    @ypercube I mean if two users store different data at the same time and then get the last insert row by date. One of the two will get the wrong row. – PiTheNumber Nov 25 '14 at 13:06
  • @PiTheNumber Right. That's why I said this is an application (PHP) problem. If two users/sessions insert 2 rows (1 each) then they should both know the user_id and the group_id they used. No need to query the database. I should probably delete my answer. – ypercubeᵀᴹ Nov 25 '14 at 13:25