Dapatkan ID dari The Last Inserted Record
Jika kita melakukan INSERT atau UPDATE pada tabel dengan bidang AUTO_INCREMENT, kita bisa mendapatkan ID dari catatan yang terakhir dimasukkan / diperbarui dengan segera.
Di tabel “MyGuests”, kolom “id” adalah bidang AUTO_INCREMENT:
CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
Contoh berikut sama dengan contoh dari halaman sebelumnya (PHP Insert Data Into MySQL), kecuali bahwa kita telah menambahkan satu baris kode untuk mengambil ID dari record yang terakhir dimasukkan. Kita juga menggemakan ID yang terakhir dimasukkan:
Contoh (MySQLi Object-oriented) :
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Buat Koneksi $conn = new mysqli($servername, $username, $password, $dbname); // Periksa Koneksi if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; if ($conn->query($sql) === TRUE) { $last_id = $conn->insert_id; echo "New record created successfully. Last inserted ID is: " . $last_id; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); ?>
Contoh (MySQLi Prosedural) :
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Buat Koneksi
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Periksa Koneksi
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
$last_id = mysqli_insert_id($conn);
echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>Contoh (PDO) :
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Atu mode error PDO ke exceptions $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; // gunakan exec() karena tidak ada hasil yang dikembalikan $conn->exec($sql); $last_id = $conn->lastInsertId(); echo "New record created successfully. Last inserted ID is: " . $last_id; } catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); } $conn = null; ?>
