JIYIK CN >

This article briefly introduces the PHP mysqli()function and demonstrates how to use it to get the last inserted ID from a MySQL database.

PHP mysqli() Function

It is an extended version of the MySQL driver called mysqli and is typically used with PHP server-side scripts to connect to a MySQL database or schema.

First, you must connect to your MySQL database. The connect()or mysqli_connect()function is used to open a connection to a MySQL server.

Before using this function, you must create mysqli()a object that 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";
    }
?>

If the connection is not established, the above code will notify the user; otherwise, it will print connected on the browser screen.

Parameter Description

Get the last inserted ID using PHP mysqli() function

To accomplish this task, you must create a table with an AUTO_INCREMENT field that will return the last inserted record ID.

Why is there AUTO_INCREMENT? Auto-increment automatically creates a unique number whenever a new record is inserted into a table.

It is usually the primary key field that we want to automatically generate every time a new record is inserted. See the following code to understand how we create an AUTO_INCREMENT column:

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

An INT column will store integer values, while AUTO_INCREMENT will automatically increase the value of the column when new records are inserted. Finally, the PRIMARY KEY sets a constraint that only allows unique values ​​for the column.

The second column is of type VARCHAR which can accept string values. To get the last inserted id, mysqli() provides the insert_id command; check it out 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, $mysqli -> query()the function executes the given query against the selected database and compares the return values.

If TRUE, we use printf()the function to output the formatted string; otherwise, it uses $mysqli -> errorto print errors.

The arguments you supply to printf()the function are inserted into the original string you supply after the % sign. The %d sign indicates that the value to be inserted is a signed decimal number (negative, positive, or zero).