PHP MySQL Insert and Return ID

In PHP, when we insert data into MySQL (database), we use queries and it returns the ID of the inserted data.

To give you a clear explanation of the process, we will have the examples below.

The examples below will give you hints of different ways of inserting data in MySQL.

The methods are possible through PHP which then will return the ID of the data.

How to Get the ID of the Last Inserted Row?

In PHP programming, we can insert and update data into MySQL.

You just have to make sure that you have a structured database or table in your MySQL that will serve as the data storage.

Moreover, you also need to assign the ID column of your table as an AUTO_INCREMENT field.

As soon as you insert or update the data from the MySQL database, you can also get the ID of that information.

In the example below, we will use the table “Sample” present in the MySQL server with its ID column set as AUTO_INCREMENT to insert data through PHP.

Sample Database:

CREATE TABLE Sample ( 
ID INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstname VARCHAR(50) NOT NULL, 
lastname VARCHAR(50) NOT NULL, 
gender VARCHAR(50) NOT NULL, 
age INT(11) NOT NULL 
);

In the sample database, we create table naming Sample and it has five attributes or columns.

The first column which is ID with its data type as an integer (INT) and has its characters length of eleven (11).

The ID attribute of the columns is AUTO_INCREMENT to make sure that there will be no duplicates of ID among the data.

Next, we also have the firstname, lastname, and gender attributes with variable characters (VARCHAR) as their data types.

Their data are limited to 50 characters and NOT NULL which means that their field or column must not be blank.

Lastly, we have the age attribute which is also an integer (INT), has the character length of 50, and must not be blank.

This time, we have our database available for use, so we will use it with the following examples:

Example Program on How To Get the ID of The Last Inserted Record Using (MySQLi Object Oriented)

For our first example, we will use the MySQLi Object-Oriented method to implement the PHP MySQL insert and return ID application.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sampledb";

// Create connection
$con = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($con->connect_error) {
  die("Connection failed: " . $con->connect_error);
}

$sql = "INSERT INTO sample (firstname, lastname, gender, age)
VALUES ('Jane', 'Jenner', 'Female', '25')";

if ($con->query($sql) === TRUE) {
  $lastID = $con->insert_id;
  echo "Data was successfully inserted. Last ID inserted is: " . $lastID;
} else {
  echo "Error: " . $sql . "<br>" . $con->error;
}

$con->close();
?>

This example will show the exact implementation when using the MySQLi object-oriented to connect the PHP and MySQL server.

This method will enable us to insert data into the database (local server).

Output:

The output from the first example shows that we have successfully inserted the data into MySQL.

To prove if the process was successful, see the result in the table below.

Example Program on How To Get ID of The Last Inserted Record Using (MySQLi Procedural)

This time, we will have another method of implementing PHP MySQL insert and return ID.

The method that we will use for the next example is MySQLi procedural.

The MySQLi procedural has some differences from the above example yet will produce the same process and output as the mysqli object-oriented method.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sampledb";

// Create connection
$con = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO sample (firstname, lastname, gender, age)
VALUES ('John', 'Jenner', 'Male', '26')";

if (mysqli_query($con, $sql)) {
  $lastID = mysqli_insert_id($con);
  echo "Data was successfully inserted. Last ID inserted is: " . $lastID;
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($con);
}

$con->close();
?>

So, in this example, we were also trying to insert the data from PHP to the MySQL server and it should return the ID of the inserted data.

Output:

As you can see in the output the connection between PHP and MySQL as well as the insertion of data was successful.

This proves that the previous method and the mysqli procedural method have similar output.

See the result below as proof of the successful query and connection.

Example Program on How To Get ID of The Last Inserted Record Using (PDO)

To insert information or data to MySQL from PHP, we can also use the PDO and return the data’s ID.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sampledb";

try {
  $con = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "INSERT INTO sample (firstname, lastname, gender, age)
  VALUES ('Jade', 'Jenner', 'Female', '14')";
  // use exec() because no results are returned
  $con->exec($sql);
  $lastID = $con->lastInsertId();
  echo "Data was successfully inserted. Last ID inserted is: " . $lastID;
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$con = null;
?>

This example will test if the PDO method is applicable and works the same with mysqli object-oriented and mysqli procedural methods.

Output:

So the output shows that we can also use the PDO method to insert data from PHP to the MySQL server.

This means that with either of the three methods, we can still implement the application of PHP and MySQL connection as well as the data insertion.

As proof of the successful process, below is the screenshot of the table result upon running the example program above.

Related Articles

• MySQL Insert, Multiple Insert, and Last Inserted Query in PHP
• PHP Remove Element From Array With Examples
• Creating a file in PHP With Examples
• Creating a file in PHP With Examples
• PHP trait With Detailed Explanation

Conclusion

In conclusion, this topic gives you three alternative methods that you can use to apply the PHP MySQL insert and return ID.

This topic enlightens you with the new methods that you can try and is advantageous for web development skills.

This discussion gives the edge and boosts your skills which preps you for a new chapter of your journey.

If you’d like to add or open a concern about this, you can leave a comment below. Good day!

Mary Grace G. Patulada

Programmer & Technical Writer at PIES IT Solution

Mary Grace G. Patulada (pen name ‘Nym’) is a programmer and writer at PIES IT Solution with a BSIT background from Carlos Hilado Memorial State College, Binalbagan Campus. Authored 370+ UML diagram tutorials and capstone documentation guides at itsourcecode.com. Specializes in UML (class, use case, activity, sequence, component, deployment), DFD, and ER diagrams for BSIT capstone projects.

Expertise: UML Diagrams · DFD · ER Diagrams · Use Case Diagrams · Activity Diagrams · Capstone Documentation · PHP  · View all posts by Mary Grace G. Patulada →