MySQLi Insert ID

Retrieve the last ID generated by an INSERT query on a table with an AUTO_INCREMENT column.

Object-oriented Style

$id = $conn->insert_id;

Procedural Style

$id = mysqli_insert_id($conn);

Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

Insert id when updating rows

Normally an UPDATE statement does not return an insert id, since an AUTO_INCREMENT id is only returned when a new row has been saved (or inserted). One way of making updates to the new id is to use INSERT ... ON DUPLICATE KEY UPDATE syntax for updating.

Setup for examples to follow:

CREATE TABLE iodku (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    misc INT NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
) ENGINE=InnoDB;

INSERT INTO iodku (name, misc)
    VALUES
    ('Leslie', 123),
    ('Sally', 456);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
+----+--------+------+
| id | name   | misc |
+----+--------+------+
|  1 | Leslie |  123 |
|  2 | Sally  |  456 |
+----+--------+------+

The case of IODKU performing an “update” and LAST_INSERT_ID() retrieving the relevant id:

$sql = "INSERT INTO iodku (name, misc)
    VALUES
    ('Sally', 3333)            -- should update
    ON DUPLICATE KEY UPDATE    -- `name` will trigger "duplicate key"
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc)";
$conn->query($sql);
$id = $conn->insert_id;        -- picking up existing value (2)

The case where IODKU performs an “insert” and LAST_INSERT_ID() retrieves the new id:

$sql = "INSERT INTO iodku (name, misc)
    VALUES
    ('Dana', 789)            -- Should insert
    ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc);
$conn->query($sql);
$id = $conn->insert_id;      -- picking up new value (3)

Resulting table contents:

SELECT * FROM iodku;
+----+--------+------+
| id | name   | misc |
+----+--------+------+
|  1 | Leslie |  123 |
|  2 | Sally  | 3333 |  -- IODKU changed this
|  3 | Dana   |  789 |  -- IODKU added this
+----+--------+------+

Found a mistake? Have a question or improvement idea? Let me know.

PHP MySQLi:

* PHP MySQLi

* Close connection

* MySQLi connect

* Loop through MySQLi results

* Prepared statements in MySQLi

* Escaping Strings

* MySQLi query

* Debugging SQL in MySQLi

* How to get data from a prepared statement

* MySQLi Insert ID

Table Of Contents

0 Getting started

1 Variables

2 Arrays

3 Functional programming

4 Types

5 Autoloading primer

6 Exception handling and error reporting

7 Working with dates and time

8 Sending email

9 Sessions

10 Cookies

11 Classes and objects

12 Password hashing

13 Output buffering

14 JSON

15 SOAP

16 Reflection

17 cURL

18 Dependency injection

19 XML

20 Regular expressions

21 Traits

22 Namespaces

23 Parsing HTML

24 Composer dependency manager

25 Magic methods

26 Alternative syntax for control structures

27 File handling

28 Magic constants

29 Type hinting

30 Multi-threading extension

31 Filters, filter functions

32 Generators

33 Operators

34 Constants

35 UTF-8

36 URLs

37 Object serialization

38 PHPDoc

39 Contributing to PHP Manual

40 String parsing

41 Loops

42 Control structures

43 Serialization

44 Closur

45 Reading request data

46 Type juggling and non-strict comparison issues

47 Security

48 PHP MySQLi

49 Command Line Interface CLI

50 Localization

51 Debugging

52 Superglobal variables

53 Unit testing

54 Variable scope

55 References

56 Compilation errors and warning

57 Installing PHP on Windows

58 DateTime class

59 Headers manipulation

60 Performance

61 Common Errors

62 Installing on Linux / Unix

63 Contributing to PHP Core

64 Coding conventions

65 Using MongoDB

66 Asynchronous programming

67 Using SQLSRV

68 Unicode Support

69 Functions

70 Create PDF files

71 Hot to detect client IP address

72 YAML

73 Image processing with GD

74 Multiprocessing

75 SOAP Server

76 Machine learning

77 Cache

78 Streams

79 Array iteration

80 Cryptography

81 PDO

82 SQLite3

83 Sockets

84 Outputting the value of a variable

85 String formatting

86 Compile PHP extensions

87 MongoDB

88 Manipulating an array

89 Executing upon an array

90 Processing multiple arrays together

91 SPL data structures

92 Comments

93 IMAP

94 Redis

95 Imagick

96 SimplXML

97 HTTP Authentication

98 Recipies

99 BC Math Binary Calculator

100 Docker deployment

101 WebSockets

102 APCu

103 Design patterns

104 Secure remember me

105 PHP mysqli

106 PHP built-in server

107 How to break down an URL

108 PSR

109 Contributors