lastInsertID returning 0, even with a persistent Connection

Question

im using the function PDO::lastInsertID (also happening with mysqli_insert_id), but it's always returning 0.

I already looked up the problem and saw that this should've fixed the problem: MySQL: LAST_INSERT_ID() returns 0 by turning on persistentConnections in the phpmyadmin "config.inc.php" file, but the problem still remains...

my table 'reservations' has a primary key which AUTO_INCREMENTs.

here is my code:

I got a button on my site which calls this javascript code:

function sonderbuchung()
{
    setReservationType();
    getSonderbuchungID(); 
}


function getSonderbuchungID() {
   $.ajax({
      url:'sonderbuchungEditID.php',
      complete: function (response) {
          $('#output').html(response.responseText);
      },
      error: function () {
          $('#output').html('Bummer: there was an error!');
      }
  });
  return false;
}

function setReservationType()
{
   $.ajax({
    url: "reservationType.php",
    type: "POST",
    data: 'reservationtype=sonderbuchung',
    success: function(data) {
        $('#output').html(data);   
    },
    error: function(data) {
        $('#output').html(data.responseText)
    },  
});

}

On my mySQL server there is a random String generated after a Insert happend, and now I want to get the random String by looking at the last Inserted ID and taking it's randomString. (Not implemented yet obviously 'cause of this problem)

sonderbuchungEditID.php:

<?php
require_once('bdd.php'); //Database connection
echo($bdd->lastInsertID());
?>

reservationType.php (all fine working, just for the sake of all code)

<?php
require_once('bdd.php');

if(isset($_POST['reservationtype'])){

$reservationtype = $_POST['reservationtype'];

$sql = "INSERT INTO reservations(reservationtype) values ('$reservationtype')";

$query = $bdd->prepare($sql);
if ($query == false) {
     file_put_contents('LOGname.txt', print_r($bdd->errorInfo(), true));

     die ('Error prepairing');

    }
    $sth = $query->execute();
    if ($sth == false) {
     file_put_contents('LOGname.txt', print_r($query->errorInfo(), true));
     die ('Error executing');
    }

}


?>

You are calling get last insert ID without inserting anything. It only works immediately after the insert statement is executed. — Sloan Thrasher, Jul 12 '17 at 15:52
So I should use lastInsertID and echo it directly in "reservationType.php"? — JSAN L., Jul 12 '17 at 15:59
Another thought. If you want the last row inserted by **any user**, if you have an autoincrementing primary key, just get the highest value of that column. That will be the last row inserted. — Sloan Thrasher, Jul 12 '17 at 16:04

score 0 · Accepted Answer · answered Jul 12 '17 at 15:58

You would need to capture and store the ID when the row is inserted.

sonderbuchungEditID.php:

<?php
    echo isset($_SESSION['last_id']) ? $_SESSION['last_id'] : "-1";
?>

reservationType.php:

<?php
require_once('bdd.php');

if(isset($_POST['reservationtype'])){
    $reservationtype = $_POST['reservationtype'];
    $sql = "INSERT INTO reservations(reservationtype) values ('$reservationtype')";
    $query = $bdd->prepare($sql);
    if ($query == false) {
        file_put_contents('LOGname.txt', print_r($bdd->errorInfo(), true));
        die ('Error prepairing');
    }
    $sth = $query->execute();
    if ($sth == false) {
        file_put_contents('LOGname.txt', print_r($query->errorInfo(), true));
        die ('Error executing');
    } else {
        //  Remember the last ID inserted.
        $_SESSION['last_id'] = $bdd->lastInsertID();
    }
}


?>

Just as a quick Info for anyone else: You first have to start a session for this to work! — JSAN L., Jul 12 '17 at 16:11

lastInsertID returning 0, even with a persistent Connection

1 Answers1