PHP: Hypertext Preprocessor (original) (raw)

mysqli::$insert_id

mysqli_insert_id

(PHP 5, PHP 7, PHP 8)

mysqli::$insert_id -- mysqli_insert_id — Returns the value generated for an AUTO_INCREMENT column by the last query

Description

Object-oriented style

Procedural style

Performing an INSERT or UPDATE statement using the LAST_INSERT_ID() MySQL function will also modify the value returned by mysqli_insert_id(). If LAST_INSERT_ID(expr) was used to generate the value ofAUTO_INCREMENT, it returns the value of the last expr instead of the generated AUTO_INCREMENT value.

Returns 0 if the previous statement did not change an AUTO_INCREMENT value. mysqli_insert_id() must be called immediately after the statement that generated the value.

Return Values

The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

Only statements issued using the current connection affect the return value. The value is not affected by statements issued using other connections or clients.

Note:

If the number is greater than the maximum int value, it will be returned as a string.

Examples

Example #1 $mysqli->insert_id example

Object-oriented style

`<?php

mysqli_report

(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); mysqli=newmysqli("localhost","myuser","mypassword","world");mysqli = new mysqli("localhost", "my_user", "my_password", "world");mysqli=newmysqli("localhost","myu​ser","myp​assword","world");mysqli->query("CREATE TABLE myCity LIKE City");$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)"; mysqli−>query(mysqli->query(mysqli−>query(query);printf("New record has ID %d.\n", $mysqli->insert_id);/* drop table */
$mysqli->query("DROP TABLE myCity");`

Procedural style

`<?php

mysqli_report

(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); link=mysqliconnect("localhost","myuser","mypassword","world");mysqliquery(link = mysqli_connect("localhost", "my_user", "my_password", "world");mysqli_query(link=mysqlic​onnect("localhost","myu​ser","myp​assword","world");mysqliq​uery(link, "CREATE TABLE myCity LIKE City");$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
mysqli_query($link, query);printf("NewrecordhasIDquery);printf("New record has ID %d.\n", mysqli_insert_id(query);printf("NewrecordhasIDlink));/* drop table */
mysqli_query($link, "DROP TABLE myCity");`

The above examples will output:

Found A Problem?

will at phpfever dot com

19 years ago

`I have received many statements that the insert_id property has a bug because it "works sometimes". Keep in mind that when using the OOP approach, the actual instantiation of the mysqli class will hold the insert_id.

The following code will return nothing.
query("INSERT INTO t (field) VALUES ('value');")) { echo 'The ID is: '.$result->insert_id; } ?>

This is because the insert_id property doesn't belong to the result, but rather the actual mysqli class. This would work:

query("INSERT INTO t (field) VALUES ('value');")) { echo 'The ID is: '.$mysqli->insert_id; } ?>

`

mmulej at gmail dot com

4 years ago


```php  
$u_name = "John Doe";  
$u_email = "johndoe@example.com";
 <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">stmt = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6151em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>connection->prepare(  
 "INSERT INTO users (name, email) VALUES (?, ?)"  
);   <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>b</mi><mi>i</mi><mi>n</mi><msub><mi>d</mi><mi>p</mi></msub><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>s</mi><msup><mi>s</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;bind_param(&#x27;ss&#x27;, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.038em;vertical-align:-0.2861em;"></span><span class="mord mathnormal">bin</span><span class="mord"><span class="mord mathnormal">d</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">p</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.2861em;"><span></span></span></span></span></span></span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">am</span><span class="mopen"><span class="mopen">(</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mord mathnormal">s</span><span class="mord"><span class="mord mathnormal">s</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mpunct">,</span></span></span></span>u_name, $u_email);  
$stmt->execute();

echo $stmt->insert_id;  
```

For UPDATE you simply change query string and binding parameters accordingly, the rest stays the same.

Of course the table needs to have AUTOINCREMENT PRIMARY KEY.

adrian dot nesse dot wiik at gmail dot com

2 years ago

`If you try to INSERT a row using ON DUPLICATE KEY UPDATE, be aware that insert_id will not update if the ON DUPLICATE KEY UPDATE clause was triggered.

When you think about it, it's actually very logical since ON DUPLICATE KEY UPDATE is an UPDATE statement, and not an INSERT.

In a worst case scenario, if you're iterating over something and doing INSERTs while relying on insert_id in later code, you could be pointing at the wrong row on iterations where ON DUPLICATE KEY UPDATE is triggered!

`

bert at nospam thinc dot nl

16 years ago

`Watch out for the oo-style use of db−>insertid.Whentheinsertidexceeds231(2147483648)fetchingtheinsertidrendersawrong,toolargenumber.Youbetterusetheproceduralmysqliinsertid(db->insert_id. When the insert_id exceeds 2^31 (2147483648) fetching the insert id renders a wrong, too large number. You better use the procedural mysqli_insert_id( db−>inserti​d.Whentheinserti​dexceeds231(2147483648)fetchingtheinsertidrendersawrong,toolargenumber.Youbetterusetheproceduralmysqlii​nserti​d(db ) instead.

[EDIT by danbrown AT php DOT net: This is another prime example of the limits of 32-bit signed integers.]

`

www dot wesley at gmail dot com

5 years ago

When using "INSERT ... ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`)", the AUTO_INCREMENT will increase in an InnoDB table, but not in a MyISAM table.

Nick Baicoianu

18 years ago

`When running extended inserts on a table with an AUTO_INCREMENT field, the value of mysqli_insert_id() will equal the value of the first row inserted, not the last, as you might expect.

query("INSERT INTO mytable (field1,field2,field3) VALUES ('val1','val2','val3'), ('val1','val2','val3'), ('val1','val2','val3')"); echo $db->insert_id; //will echo the id of the FIRST row inserted ?>

`

jpage at chatterbox dot fyi

2 years ago

`What is unclear is how concurrency control affects this function. When you make two successive calls to mysql where the result of the second depends on the first, another user may have done an insert in the meantime.

The documentation is silent on this, so I always determine the value of an auto increment before and after an insert to guard against this.

`

alan at commondream dot net

20 years ago

I was having problems with getting the inserted id, and did a bit of testing. It ended up that if you commit a transaction before getting the last inserted id, it returns 0 every time, but if you get the last inserted id before committing the transaction, you get the correct value.