Menu
I need to get the last inserted id of table that have multi-column primary keys.
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","[email protected]")
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.
474
Each table can only have one primary key. Access can automatically create a primary key field for you when you create a table, or you can specify the fields that you want to use as the primary key. This article explains how and why to use primary keys. To set a table's primary key, open the table in Design view.
A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).
You can only have one primary key, but you can have multiple columns in your primary key. You can also have Unique Indexes on your table, which will work a bit like a primary key in that they will enforce unique values, and will speed up querying of those values. RB. RB.
You can create a MySQL composite Primary Key in the following two ways: During table creation using CREATE TABLE statement. After creating tables using ALTER TABLE statement.
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.
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
