PHP Get ID Of Last Inserted Data In MySQL

Submitted by: 
Language: 
Visitors have accessed this post 2657 times.

PHP Get ID Of Last Inserted Data In MySQL

After creating the tutorial for Inserting Data and Updating Data In MySQL, we can use this to get the ID for the last data that we inserted or updated in the table.

For example, our table is “tbl_registration” and the ID is an AUTO_INCREMENT.
You can visit the previous tutorial Inserting Data and Updating Data In MySQL to join this source code for retrieving the Last ID inserted or updated data. And, we can print the last ID.

  1. -- --------------------------------------------------------
  2.  
  3. --
  4. -- Table structure for table `tbl_registration`
  5. --
  6.  
  7. CREATE TABLE `tbl_registration` (
  8. `tbl_registration_id` INT(11) NOT NULL,
  9. `first_name` VARCHAR(100) NOT NULL,
  10. `middle_name` VARCHAR(100) NOT NULL,
  11. `last_name` VARCHAR(100) NOT NULL,
  12. `email` VARCHAR(100) NOT NULL,
  13. `contact_number` VARCHAR(100) NOT NULL
  14. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Using MySQLi And PDO To Get The Last ID Inserted Data In MySQL

Result

Using MySQLi (Object-Oriented)

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_query_pdo";
  6.  
  7. // Create connection
  8. $conn = new mysqli($servername, $username, $password, $dbname);
  9. // Check connection
  10. if ($conn->connect_error) {
  11. die("Connection failed: " . $conn->connect_error);
  12. }
  13.  
  14. $first_name=$_POST['first_name'];
  15. $middle_name=$_POST['middle_name'];
  16. $last_name=$_POST['last_name'];
  17. $email=$_POST['email'];
  18. $contact_number=$_POST['contact_number'];
  19.  
  20. $sql = "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  21. VALUES ('$first_name', '$middle_name', '$last_name', '$email', '$contact_number')";
  22.  
  23. if ($conn->query($sql) === TRUE) {
  24. $last_id = $conn->insert_id;
  25. echo "New record created successfully. Last inserted ID is: " . $last_id;
  26. } else {
  27. echo "Error: " . $sql . "<br>" . $conn->error;
  28. }
  29.  
  30. $conn->close();
  31. ?>

Using MySQLi (Procedural)

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_query_pdo";
  6.  
  7. // Create connection
  8. $conn = mysqli_connect($servername, $username, $password, $dbname);
  9. // Check connection
  10. if (!$conn) {
  11. die("Connection failed: " . mysqli_connect_error());
  12. }
  13.  
  14. $first_name=$_POST['first_name'];
  15. $middle_name=$_POST['middle_name'];
  16. $last_name=$_POST['last_name'];
  17. $email=$_POST['email'];
  18. $contact_number=$_POST['contact_number'];
  19.  
  20. $sql = "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  21. VALUES ('$first_name', '$middle_name', '$last_name', '$email', '$contact_number')";
  22.  
  23. if (mysqli_query($conn, $sql)) {
  24. $last_id = mysqli_insert_id($conn);
  25. echo "New record created successfully. Last inserted ID is: " . $last_id;
  26. } else {
  27. echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  28. }
  29.  
  30. mysqli_close($conn);
  31. ?>

Using PDO (PHP Data Objects)

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_query_pdo";
  6.  
  7. try {
  8. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  9. // set the PDO error mode to exception
  10. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  11.  
  12. $first_name=$_POST['first_name'];
  13. $middle_name=$_POST['middle_name'];
  14. $last_name=$_POST['last_name'];
  15. $email=$_POST['email'];
  16. $contact_number=$_POST['contact_number'];
  17.  
  18. $sql = "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  19. VALUES ('$first_name', '$middle_name', '$last_name', '$email', '$contact_number')";
  20. // use exec() because no results are returned
  21. $conn->exec($sql);
  22. $last_id = $conn->lastInsertId();
  23. echo "New record created successfully. Last inserted ID is: " . $last_id;
  24. }
  25. catch(PDOException $e)
  26. {
  27. echo $sql . "<br>" . $e->getMessage();
  28. }
  29.  
  30. $conn = null;
  31. ?>

So what can you say about this work? Share your thoughts in the comment section below or email me at [email protected]. Practice Coding. Thank you very much.


Comments

this article is great and easy t0 learn

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.