Get auto increment row ID when inserting with PDO

Question

I am inserting new data into a MySQL and I have a column called "bookingid" that is the primary key and has auto increment. I need to get the number of the auto increment after inserting my data, here is my code:

    try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   // prepare sql and bind parameters
$stmt = $conn->prepare("INSERT INTO reservations (bookingdatetime, firstname, lastname, address) 
VALUES (:bookingdatetime, :firstname, :lastname, :address)");

$stmt->bindParam(':bookingdatetime', $bookingdatetime);
$stmt->bindParam(':firstname', $fname);
$stmt->bindParam(':lastname', $lname);
$stmt->bindParam(':address', $address);


// insert a row

$stmt->execute();

echo "success! row id: ";

}catch(PDOException $e){
echo json_encode(array("title" => "Database Error", "body" => $e->getMessage()));
}
$conn = null;

How do I echo the increment where it says "success! row id: "? thank you!

score 0 · Answer 1 · answered Oct 02 '19 at 22:18

0

You can use lastInsertId() to retrieve the last row that was inserted:

$stmt->execute();
echo "success! row id: $stmt->lastInsertId()";

Note that lastInsertId() must be called before commit() in a transaction, otherwise it will return 0.

answered Oct 02 '19 at 22:18

Obsidian Age

41,205
10
48
71

When I put "$bookingid = $stmt->lastInsertId();" it does't work... – Oct 03 '19 at 00:37
Notice: Undefined property: PDOStatement::$lastInsertId in book.php on line 33 – Oct 03 '19 at 00:45
Needed to be $conn->lastInsertId() – Oct 03 '19 at 01:05

Get auto increment row ID when inserting with PDO

1 Answers1