PHP mysqli_insert_id()用法及代碼示例

定義和用法

如果您有一個包含 AUTO_INCREMENT 屬性的表，並且您的最後一個 MySQLi 函數調用執行了 INSERT 或 UPDATE 語句。這mysqli_insert_id()函數返回上次執行查詢的自動生成的 id。

用法

mysqli_insert_id($con)

參數

Sr.No	參數及說明
1	con(Mandatory) 這是一個表示與 MySQL 服務器的連接的對象。

返回值

PHP mysqli_insert_id() 函數返回上次查詢中 "Auto Increment" 列的值 如果是 INSERT 或 UPDATE 操作。如果上次執行的查詢不是 INSERT 或 UPDATE，或者，如果表沒有任何具有 "AUTO_INCREMENT" 屬性的列/字段，則此函數返回 0。

PHP版本

這個函數最初是在 PHP 版本 5 中引入的，適用於所有後續版本。

假設我們在數據庫 mydb 中創建了一個名為 Cricketers 的表，其中字段 ID 是 PRIMARY KEY 並且它是 AUTO INCREMENTED 作為 -

CREATE TABLE Cricketers(
   ID INT PRIMARY KEY AUTO_INCREMENT,
   First_Name VARCHAR(255), 
   Last_Name VARCHAR(255), 
   Date_Of_Birth date, 
   Place_Of_Birth VARCHAR(255), 
   Country VARCHAR(255)
);

示例

以下示例演示了 mysqli_insert_id() 函數的用法(程序風格) -

<?php
   //Creating a connection
   $con = mysqli_connect("localhost", "root", "password", "mydb");

   //Inserting a record into the employee table
   $sql = "insert into Cricketers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India')";
   mysqli_query($con, $sql);
   //Insert ID
   $id = mysqli_insert_id($con);
   print("Insert ID:".$id ."\n");

   $sql = "insert into Cricketers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica')";
   mysqli_query($con, $sql);
   $id = mysqli_insert_id($con);
   print("Insert ID:".$id);

   //Closing the connection
   mysqli_close($con);
?>

這將產生以下結果 -

Insert ID:1
Insert ID:2

示例

在麵向對象的風格中，這個函數的語法是 $con->insert_id();以下是麵向對象樣式 $minus 中此函數的示例；

<?php
   //Creating a connection
   $con = new mysqli("localhost", "root", "password", "mydb");

   //Inserting a record into the employee table
   $con -> query("insert into Cricketers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka')");
   //Insert ID
   $state = $con->insert_id;
   print("Insert ID:".$state."\n");

   //Inserting a record into the employee table
   $con -> query("insert into Cricketers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India')");
   //Insert ID
   $state = $con->insert_id;
   print("Insert ID:".$state);

   //Closing the connection
   $con -> close();
?>

這將產生以下結果 -

Insert ID:3
Insert ID:4

示例

以下是函數 mysqli_insert_id 的另一個示例 -

<?php
   //Creating a connection
   $con = mysqli_connect("localhost", "root", "password", "mydb");

   //Query to SELECT all the rows of the Cricketers table
   mysqli_query($con, "SELECT * FROM Cricketers");
   print("Insert ID (select query):".mysqli_insert_id($con)."\n");

   //Query to INSERT multiple rows into the Cricketers table
   mysqli_query($con, "INSERT INTO Cricketers VALUES (5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'), (6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India') ");
   print("Insert ID:(multiple inserts) ".mysqli_insert_id($con)."\n");

   //Query to UPDATE the rows of the employee table
   mysqli_query($con, "UPDATE Cricketers set COUNTRY = 'S.Africa' where ID = 2");
   print("Insert ID (update query):".mysqli_insert_id($con)."\n");

   //Query to INSERT a record into the employee table
   mysqli_query($con, "INSERT INTO employee VALUES ('Sarmista', 'Sharma', 28, 'F', 15000,  101)");
   print("Insert ID:(table with out auto incremented key) ".mysqli_insert_id($con)."\n");

   //Closing the connection
   mysqli_close($con);
?>

這將產生以下結果 -

Insert ID (select query):0
Insert ID:(multiple inserts) 6
Insert ID (update query):0
Insert ID:(table with out auto incremented key) 0

示例

以下示例演示了 mysqli_insert_id 函數與 SELECT、UPDATE、INSERT 和 DELETE 查詢的用法 -

<?php
   $connection_mysql = mysqli_connect("localhost", "root", "password", "mydb");
   
   if (mysqli_connect_errno($connection_mysql)){
      echo "Failed to connect to MySQL:" . mysqli_connect_error();
   }
   
   mysqli_query($connection_mysql,"INSERT INTO Employee (name) VALUES('PHP')");
   echo "New record has id:" . mysqli_insert_id($connection_mysql); 
   
   mysqli_close($connection_mysql);
?>

這將產生以下結果 -

New record has id:0