How to get last inserted ID after insert to MySQL table with multi col primary key

Question

I have this table:

CREATE TABLE `test` (
  `Id1` int(11) unsigned NOT NULL,
  `Id2` int(11) unsigned NOT NULL,
  `Id3` int(11) unsigned NOT NULL,
  `Id4` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  PRIMARY KEY (`Id1`,`Id2`,`Id3`,`Id4`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

INSERT INTO `test` (`Id1`, `Id2`, `Id3`, `Name`) VALUES (1, 1, 1, 'test')
INSERT INTO `test` (`Id1`, `Id2`, `Id3`, `Name`) VALUES (1, 1, 1, 'test2')
INSERT INTO `test` (`Id1`, `Id2`, `Id3`, `Name`) VALUES (1, 2, 1, 'test')
INSERT INTO `test` (`Id1`, `Id2`, `Id3`, `Name`) VALUES (1, 2, 1, 'test2')

After creating the table the date looks like this:

Id1   Id2   Id3   Id4   Name

1     1     1     1     test
1     1     1     2     test2
1     2     1     1     test
1     2     1     2     test2

Id4 is incrementing as is expected but I have problem to get inserted Id4 using PHP mysqli. This is the code I am using:

$db = new mysqli('host', 'user', 'pass', 'db');
$db->query("INSERT INTO `test` (`Id1`, `Id2`, `Id3`, `Name`) VALUES (1, 1, 1, 'test')");
var_dump($db->insert_id);

I get result

int(0)

Result should be int(1) but I get zero for no reason. Any ideas?

can you add `AUTO_INCREMENT=1` to the end of `create table` (after 'DYNAMIC') and give it a new try? — Jeff, May 15 '16 at 16:29
@Fred-ii- But this is exactly what he just did and it didn't work for him... or am i missing something? He is using the Object Oriented Style which is synonymous with what you just posted for the Procedural Style. — Poiz, May 15 '16 at 16:43
@Fred-ii-: He does have a column that has the AUTO_INCREMENT attribute, doesn't he? — Jeff, May 15 '16 at 16:43
*"My problem is to get Id4 after row insert."* - There is no `Id4` in your query. Why don't you show us your real code? — Funk Forty Niner, May 15 '16 at 16:52
@Fred-ii- I have a feeling that you'll have to delete this one as well. — Your Common Sense, May 15 '16 at 17:07
@YourCommonSense Nope, I kind of like it there actually. As the saying goes: *"Bad publicity, is still publicity"* ;-) — Funk Forty Niner, May 15 '16 at 17:16
I did some modifications in my question to be more specific. — sajushko, May 15 '16 at 17:43
Any time you find yourself with enumerated column names (above '2', say) you can be pretty much certain that your design is flawed — Strawberry, May 15 '16 at 17:49
Enumerated column means Id1, Id2... ? This is just an example. My actual cols have different names that make sense. — sajushko, May 15 '16 at 17:53
@YourCommonSense This question is duplicate to what? I am not able to find any other question like this on stackoverflow. Can you please provide a link? — sajushko, May 15 '16 at 17:55

Poiz · Accepted Answer · 2016-10-05T13:40:05.410

1

Essentially, your code should work... it worked when tested... in other words check to see if there is something else going on... as it stands, your code is ok.... and should work fine... However as an alternative work-around, you may want to wrap MySql LAST_INSERT_ID() Function in your own Function and then call the Function when you need to get the Last Insert ID...

    <?php 

    // WILL RETURN THE ID OF THE AUTO-INCREMENTED FIELD ONLY
    function getLastInsertID($db, $tbl='test'){
        $result = null;
        $sql    = $db->prepare("SELECT LAST_INSERT_ID() FROM `". $tbl ."`");
        $sql->execute();
        $sql->bind_result($result);
        $sql->fetch();
        return $result;
    }
    var_dump(getLastInsertID($db));

Perhaps it does the Trick for you....

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

edited Oct 05 '16 at 13:40

answered May 15 '16 at 16:38

Poiz

7,611
2
15
17

What's wrong with mysqli API function? – Your Common Sense May 15 '16 at 17:06
@YourCommonSense Only common sense, man! Sometimes it can be very Uncommon – Poiz May 15 '16 at 17:13
Thanks. This solve my problem. $mysqli->insert_id return 0 while actual SQL request returns correct value. It looks like there is really something wrong with API function. – sajushko May 15 '16 at 17:47
@sajushko Good! Happy it worked for you. The Problem could stem from a lot of causes especially in this case but Nice it worked. You may just mark this as the Answer so that anyone with a Similar issue in the Future may benefit from it... – Poiz May 15 '16 at 17:54
@Poiz marked :) Thanks a lot again. – sajushko May 15 '16 at 17:56
@YourCommonSense ***Never argue with stupid people, they will drag you down to their level & then beat you with experience.*** Ironically enough, **within the Stupid dwells the Wisest! The Grandest of Fools does in the Wisest as well dwell.** Tell me: **who can describe an Apple that has never tasted 1?** ***How can 1 recognise a Fool, without himself knowing folly?*** The Paradoxes of Life, Wisdom & Foolishness go way beyond Logic, Coding & Poetry. They are experiences which can only be tasted, known & owned: for in the Fool, the Wise waits as in the Wise the Fool mocks... **ALL IN TIME** – Poiz May 16 '16 at 01:28
@YourCommonSense Even the Lord of Sciences (Einstein) understood this: **ONLY 2 THINGS ARE INFINITE, THE UNIVERSE & HUMAN STUPIDITY. AND I'M NOT SURE ABOUT THE FORMER.** He also noted something more interesting, Quote: ***I FEAR THE DAY THAT TECHNOLOGY WILL SURPASS OUR HUMAN INTERACTION. THE WORLD WILL HAVE A GENERATION OF IDIOTS!*** Mark Twain or Einstein? ***MySqli Api Functions or simply collaborated with a Fellow to solve the issue @Hand?*** https://imgur.com/a/HeQc5 – Poiz May 16 '16 at 01:51
@YourCommonSense The same solution down-voted as wrong and not following convention, was the solution to the OP's issue. In his words: ***This solve my problem. $mysqli->insert_id return 0 while actual SQL request returns correct value. It looks like there is really something wrong with API function.*** In order words, ***Maybe even gods do bleed too...*** But then, didn't Einstein just say that **Stupidity is Infinite?** - The only difference being that ***i embrace mine and thus i know no more...*** this indeed is also part of my ***COMMON SENSE***!!! – Poiz May 16 '16 at 02:08

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

LAST_INSERT_ID() returns the value only for a column declared AUTO_INCREMENT. There's no function to return the value in a compound primary key that wasn't generated by the system. You ought to know that value already, since you just gave it in an INSERT statement. The tricky case would be when a trigger or something overrides the value.

More details: Check this stackoverflow link

edited May 23 '17 at 10:34

Community

1
1

answered May 15 '16 at 17:04

Ranjeet Singh

588
5
12

Id4 is AUTO_INCREMENT – sajushko May 15 '16 at 17:28
Try with InnoDB. Hope it will help. – Ranjeet Singh May 15 '16 at 17:45
Engine is not a problem. There was problem with mysqli_insert_id function. This function returns 0 while actual LAST_INSERT_ID() request returns correct value. – sajushko May 15 '16 at 17:50

How to get last inserted ID after insert to MySQL table with multi col primary key

2 Answers2