mysqli::init
Note that if you Call a MySQL stored procedure to insert a new record and then reference $db->insert_id; you will get 0 back, not the last inserted ID.
It is therefore necessary to add a line to your MySQL Stored Procedure such as
select last_insert_id() as intRecordKey;
after the insert so that the query will return the new key value.
Then in your php code you can use the following
<?php
$objInsertResult = $db->query($strSQL);
$objInsertRow = $objInsertResult->fetch_object();
$intRecordKey = $objInsertRow->intRecordKey;
?>
It would however be nice if $db->insert_id did return the last insert ID following a stored procedure call.
mysqli->insert_id
mysqli_insert_id
(PHP 5)
mysqli->insert_id -- mysqli_insert_id — 直近のクエリで使用した自動生成の ID を返す
説明
オブジェクト指向型（プロパティ）:
手続き型:
mysqli_insert_id() 関数は、AUTO_INCREMENT 属性を 持つカラムがあるテーブル上でのクエリにより生成された ID を返します。 直近のクエリが INSERT あるいは UPDATE ではなかった場合、あるいは 変更されたテーブルに AUTO_INCREMENT 属性を持つカラムがなかった場合は この関数はゼロを返します。
注意: LAST_INSERT_ID() 関数を使用して INSERT あるいは UPDATE ステートメントを 実行すると、mysqli_insert_id() 関数の返す値も 変更されます。
返り値
直前のクエリで更新された AUTO_INCREMENT フィールドの値を返します。接続での直前のクエリがない場合や クエリが AUTO_INCREMENT の値を更新しなかった場合は ゼロを返します。
注意: もし数値が int の最大値をこえた場合、mysqli_insert_id() は文字列で結果を返します。
例
例1 オブジェクト指向型
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* 接続状況をチェックします */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TABLE myCity LIKE City");
$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);
printf ("New Record has id %d.\n", $mysqli->insert_id);
/* テーブルを削除します */
$mysqli->query("DROP TABLE myCity");
/* 接続を閉じます */
$mysqli->close();
?>
例2 手続き型
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* 接続状況をチェックします */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CREATE TABLE myCity LIKE City");
$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
mysqli_query($link, $query);
printf ("New Record has id %d.\n", mysqli_insert_id($link));
/* テーブルを削除します */
mysqli_query($link, "DROP TABLE myCity");
/* 接続を閉じます */
mysqli_close($link);
?>
上の例の出力は以下となります。
New Record has id 1.
add a note
User Contributed Notesmysqli->insert_id
fizzyorange at gmail dot com
02-Apr-2009 12:27
02-Apr-2009 12:27
bert at nospam thinc dot nl
23-Jul-2008 12:58
23-Jul-2008 12:58
Watch out for the oo-style use of $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 ) instead.
[EDIT by danbrown AT php DOT net: This is another prime example of the limits of 32-bit signed integers.]
Nick Baicoianu
04-May-2007 05:10
04-May-2007 05:10
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.
<?
//mytable has an auto_increment field
$db->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
?>
will at phpfever dot com
21-Apr-2006 10:40
21-Apr-2006 10:40
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.
<?php
$mysqli = new mysqli('host','user','pass','db');
if ($result = $mysqli->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:
<?php
$mysqli = new mysqli('host','user','pass','db');
if ($result = $mysqli->query("INSERT INTO t (field) VALUES ('value');")) {
echo 'The ID is: '.$mysqli->insert_id;
}
?>
matix.bg a gmail
14-Mar-2006 07:56
14-Mar-2006 07:56
Some people are wondering how to get the ids of the rows inserted with loop. Here is it:
<?php
// stmt_init ...
for (;;;) {
// do some things...
$stmt->execute();
$ids[] = $mysqli->insert_id;
}
?>
alan at commondream dot net
04-Nov-2004 04:44
04-Nov-2004 04:44
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.