How to Use PHP MySQLi Function to Fetch Last Inserted ID

1. PHP mysqli() Function
2. Use PHP mysqli() Function to Fetch Last Inserted ID

This tutorial briefly introduces the PHP mysqli() function and demonstrates how to use it to fetch the last inserted id from the MySQL database.

PHP mysqli() Function

It is a version of MySQL driver extension called mysqli, often used with the PHP server-side scripting to connect with the MySQL databases or schemas.

To get started, first, you must connect with your MySQL database. The connect() or mysqli_connect() function is used to open a connection to the MySQL server.

Before using the function, you must create the mysqli() object, which contains parameters about your MySQL server.

    <?php
        $mysqli = new mysqli(
                            "host_name",
                            "username",
                            "password",
                            "database_name",
                            "port",
                            "socket"
                  );

        //Checking connection
        if ($mysqli -> connect_errno){
            echo "Something went wrong. Failed to connect with MySQL database".
                $mysqli -> connection_error;
            exit();
        }
        else{
             echo "Connected";
        }
?>

The above code will inform the user if the connection is not established; otherwise, it will print Connected on the browser screen.

Parameters Description

Use PHP mysqli() Function to Fetch Last Inserted ID

To achieve this task, you must create a table with an AUTO_INCREMENT field, which will return the last inserted record id.

Why have AUTO_INCREMENT? Whenever the new record is inserted into a table, auto-increment enables a unique number to be created automatically.

It is frequently the primary key field we want to be automatically produced each time a new record is inserted. See the following code to know how we can create an AUTO_INCREMENT column:

CREATE TABLE table_name(
    column_name1 INT AUTO_INCREMENT PRIMARY KEY,
    column_name2 VARCHAR(100)
);

The INT column will store integer values, and AUTO_INCREMENT will automatically increment the column’s value whenever a new record is inserted. Finally, the PRIMARY KEY sets up the constraint, only allowing unique values for the column.

The second column is of the VARCHAR type that can accept string values. To fetch the last inserted id, mysqli() provides the insert_id command; check it below:

$mysql->insert_id;

To see this command in action, first, run an INSERT query, then run the insert_id command to see the last inserted id:

<?php
    $mysqli = new mysqli(
                         "host_name",
                         "username",
                         "password",
                         "database_name",
                         "port",
                         "socket"
              );

        // Checking connection
       if ($mysqli -> connect_errno){
           echo "Something went wrong. Failed to connect with MySQL database".
               $mysqli -> connection_error;
           exit();
       }else{
           echo "Connected";
       }

       $sql_query = "INSERT INTO table_name (column_name2) VALUES ('value_2')";

       if ($mysqli->query($sql_query) === TRUE) {
           printf("Last inserted record ID %d. \n", $mysqli->insert_id);
       } else {
         echo "Error: ". $sql_query . "<br>" . $mysqli->error;
       }
?>

In the above code, the $mysqli -> query() function executes your given query against selected database. and compares the returned value.

If it is TRUE, we use the printf() function to output a formatted string; otherwise, it will print an error using $mysqli -> error.

The parameters you provide to the printf() function will be inserted after the % sign in the original string you provided. And the %d sign means that the value which will be inserted is a Signed decimal number (negative, positive or zero).