PHP | mysqli_insert_id() Function

Hello folks! welcome back to a new edition of our tutorial on PHP. In this tutorial guide, we are going to be studying about the PHP mysqli_insert_id() Function.

The PHP mysqli_insert_id() function returns the auto generated id of the last executed query.

Syntax

Following below is the syntax to use this function -

mysqli_insert_id($con)

READ: PHP | mysqli_get_server_version() Function

Parameter Details

Sr.No	Parameter & Description
1	con(Mandatory) This is an object representing a connection to MySQL Server.

Return Value

This PHP function returns the value of the "Auto Increment" column in the last query in case it is INSERT, or an UPDATE operation. If the last executed query is not INSERT, or UPDATE or, if the table does not have any column and field with an "Auto Increment" attribute, then this built-in function returns 0.

PHP Version

This PHP function was first introduced in PHP version 5 and it works in all the later versions.

Example1

Assume we have created a table that is named Footballers in the database mydb, whereby the field ID is the PRIMARY KEY and it is Auto Incremented as -

CREATE TABLE Footballers(
   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)
);

The following example illustrates the usage of the PHP mysqli_insert_id() function (in a procedural style) -

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

   //Inserting a record into the employee table
   $sql = "insert into Footballers values(1, 'Alex', 'Iwobi', DATE('1996-05-03'), 'Lagos', 'Nigeria')";
   mysqli_query($con, $sql);
   //Insert ID
   $id = mysqli_insert_id($con);
   print("Insert ID: ".$id ."\n");

   $sql = "insert into Footballers values(2, 'Victor', 'Osimhen', DATE('1998-12-29'), 'Lagos', 'Nigeria')";
   mysqli_query($con, $sql);
   $id = mysqli_insert_id($con);
   print("Insert ID: ".$id);

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

Output

When the above code is executed, it will produce the following result -

Insert ID: 1
Insert ID: 2

Example2

In an object oriented style the syntax of this built-in function is $con->insert_id(); The following below is an example of this PHP function in an object oriented style $minus;

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

   //Inserting a record into the employee table
   $con -> query("insert into Footballers values(3, 'Samuel', 'Chukwueze', DATE('1999-05-22'), 'Umuahia', 'Nigeria')");
   //Insert ID
   $state = $con->insert_id;
   print("Insert ID: ".$state."\n");

   //Inserting a record into the employee table
   $con -> query("insert into Footballers values(4, 'Lionel', 'Messi', DATE('1987-06-24'), 'Rosario', 'Argentina')");
   //Insert ID
   $state = $con->insert_id;
   print("Insert ID: ".$state);

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

Output

When the above code is executed, it will produce the following result -

Insert ID: 3
Insert ID: 4

Example3

Following below is another example of the PHP mysqli_insert_id() function -

<?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 Footballers");
   print("Insert ID (select query): ".mysqli_insert_id($con)."\n");

   //Query to INSERT multiple rows into the Cricketers table
   mysqli_query($con, "INSERT INTO Footballers VALUES (5, 'Cristiano', 'Ronaldo', DATE('1985-02-05'), 'Funchal', 'Portugal'), (6, 'Sergio', 'Aguero', DATE('1988-06-02'), 'Buenos Aires', 'Argentina') ");
   print("Insert ID: (multiple inserts) ".mysqli_insert_id($con)."\n");

   //Query to UPDATE the rows of the employee table
   mysqli_query($con, "UPDATE Footballers set COUNTRY = 'Argentina' 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 ('Kennedy', 'Nkpara', 27, 'M', 15000,  101)");
   print("Insert ID: (table with out auto incremented key) ".mysqli_insert_id($con)."\n");

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

Output

When the above code is executed, it will produce the following result below -

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

Example4

The following example illustrates the usage of the PHP mysqli_insert_id() function with the SELECT, UPDATE, INSERT and, DELETE queries -

<?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);
?>

Output

When the above code is executed, it will produce the following result below -

New record has id: 0

READ: PHP | mysqli_init() Function

Alright guys! This is where we are rounding up for this tutorial post. In our next tutorial, we will discuss about the PHP mysqli_kill() Function.

Do feel free to ask your questions where necessary and we will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Do follow us on our various social media handles available and also subscribe to our newsletter to get our tutorial posts delivered directly to your emails.

Thanks for reading and bye for now.