lastInsertId() returns NULL

Question

i am trying to get the last id inserted in my sql query, but my varieble returns with NULL

here is my code

 $conn->beginTransaction();
try{
  $stmt=$conn->prepare("SELECT cn_id FROM food_category WHERE catname= :catname");
  $stmt->bindParam(':catname', $_POST['catname'], PDO::PARAM_STR);
  $stmt->execute();
  $catres= $stmt->fetch(PDO::FETCH_ASSOC);
  if(count($catres) > 0 ){
    $cn_id= $catres['cn_id'];
  }else{
    $stmt1= $conn->prepare("INSERT INTO food_category (catname) VALUES (:catname)");
    $stmt1->bindParam(':catname', $_POST['catname'], PDO::PARAM_STR);
    $stmt1->execute();
    $cn_id= $stmt1->lastInsertId();
  }

  $stmt2=$conn->prepare("SELECT cn_id FROM food_category_en WHERE catname= :catname_en");
  $stmt2->bindParam(':catname_en', $_POST['catname_en'], PDO::PARAM_STR);
  $stmt2->execute();
  $catresen= $stmt2->fetch(PDO::FETCH_ASSOC);
  if(count($catresen) > 0 ){
    $cn_id_en= $catresen['cn_id'];
  }else{
    $stmt3= $conn->prepare("INSERT INTO food_category_en (catname) VALUES (:catname_en)");
    $stmt3->bindParam(':catname_en', $_POST['catname_en'], PDO::PARAM_STR);
    $stmt3->execute();
    $cn_id_en= $stmt3->lastInsertId();
  }

  $stmt4=$conn->prepare("INSERT INTO food_category_main (st_id, cn_id, cn_id_en, catcount) VALUES (:st_id, :cn_id, :cn_id_en, :catcount)");
  $stmt4->bindParam(':st_id', $_POST['st_id'], PDO::PARAM_INT);
  $stmt4->bindParam(':cn_id', $cn_id, PDO::PARAM_INT);
  $stmt4->bindParam(':cn_id_en', $cn_id_en, PDO::PARAM_INT);
  $stmt4->bindParam(':catcount', $noc, PDO::PARAM_INT);
  $stmt4->execute();

$stmt5=$conn->prepare("UPDATE magazia_main SET noc= :noc WHERE st_id= :st_id");
$stmt5->bindParam(':st_id', $_SESSION['st_id'], PDO::PARAM_INT);
$stmt5->bindParam(':noc', $noc, PDO::PARAM_INT);
$stmt5->execute();

$conn->commit();
header ("Location:dashboard.php?store=".$_SESSION['name']);
exit;
   }catch(Exception $e){
//An exception has occured, which means that one of our database queries
//failed.
//Print out the error message.
echo $e->getMessage();
//Rollback the transaction.
$conn->rollBack();

 }

I read in the php manual here that if am i using transaction the lastInsertId will return 0. As you see both lastInsertId (1st in stmt1, 2nd in stmt3) are before $conn->commit();, although they return with NULL.

cn_id in food_category and cn_id in food_category_en are both primary keys and AUTO_INCREMENT

i search for some solutions in the internet but none of them seems to fix my problem.

here is a printscreen from php my admin

*** connection to database****

 $servername = "localhost";
 $username = "root";
 $password = "12345";
 try {
  $conn = new PDO("mysql:host=$servername;dbname=superdb;     charset=utf8", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }
  catch(PDOException $e)
  {
  echo "Connection failed: " . $e->getMessage();
  }
  ?>

Any ideas?

Thanks in advance

Vaggelis

hello @halojoy i tried that too but i get the same results :/ — vaggelis, Dec 23 '17 at 17:03
Are you inserting to tables with AUTO_INCREMENT columns? Are you allowing those to generate new values, or are you writing explicit id values (this suppresses the auto-increment behavior)? The lastInsertId() doesn't return the id of the row you inserted, *unless* the id value was generated by AUTO_INCREMENT. — Bill Karwin, Dec 23 '17 at 17:17
hello @Paul Spiegel, no my error log has no errors. The only error i get is from SQL, SQLSTATE[230000]: Integrity constraint violation: 1048 Column cn_id cannot be null — vaggelis, Dec 23 '17 at 17:19
`Column cn_id cannot be null` then it's **not** `AUTO_INCREMENT`. — Paul Spiegel, Dec 23 '17 at 17:20
_Integrity constraint violation: 1048 Column cn_id cannot be null_ So the query failed right! So the last insert id wont be set as the insert failed — RiggsFolly, Dec 23 '17 at 17:23
@PaulSpiegel i upadated my question with a print screen from php my admin. My tables are identical so i uploaded a single image — vaggelis, Dec 23 '17 at 17:28
Hello @RiggsFolly yes it fails because cn_id is NULL. But why cn_id is null when the value is equal to lastInsertId()? — vaggelis, Dec 23 '17 at 17:34
Then this `INSERT INTO food_category (catname) VALUES (:catname)` query must also fail assuming you are going through that part of the code — RiggsFolly, Dec 23 '17 at 17:37
if am i right the sql error triggers in stmt4 when i try to put to save the value of cn_id in the category_main table because the current value of cn_id in null. — vaggelis, Dec 23 '17 at 17:39
my database connection runs smoothly in other pages, but i will post — vaggelis, Dec 23 '17 at 17:41
When debugging code you should turn error reporting on. `$cn_id= $stmt1->lastInsertId();` must generate an error in any case, since a `PDOStatement` object doesn't have such a method (see the first comment). You should also create a minimal code example, that will reproduce the error. Usually while doing so you will find the source of the error very soon on yourself. — Paul Spiegel, Dec 23 '17 at 17:45
i turn on the error reporting, i checked again the errors log, but still nothing no errors :/ — vaggelis, Dec 23 '17 at 17:53
@vaggelis Is the data being inserted into the database sucesfully? — The Codesee, Dec 23 '17 at 17:58
Hello @TheCodesee, in other queries yes the data is successfully inserted into the tables of the database — vaggelis, Dec 23 '17 at 17:59

score 0 · Accepted Answer · answered Dec 24 '17 at 14:19

After hours of searching, i didnt find the reason why lastInsertId() returns NULL. Anyway changed my code a little bit and now is working.

I removed the SELECT queries from transaction and i put them in the if statement which checks if the input has value. After that i made two if statements in the pdo transaction to check that if the value cn_id= 0 or cn_id_en= 0.

Here is my code now:

   //check is inputs has value

   if (empty($_POST['catname'])) {
  $errors[] = 'Ξεχάσατε το όνομα της κατηγορίας!';
}else{
  $stmt=$conn->prepare("SELECT cn_id FROM food_category WHERE catname= :catname");
  $stmt->bindParam(':catname', $_POST['catname'], PDO::PARAM_STR);
  $stmt->execute();
  $catres= $stmt->fetch(PDO::FETCH_ASSOC);
  if(count($catres) > 0 ){
    $cn_id= $catres['cn_id'];
  }else{
    $cn_id=0;
  }
}
if (empty($_POST['catname_en'])) {
    $errors[] = 'Ξεχάσατε το όνομα της κατηγορίας στα Αγγλικά!';
  }else{
    $stmt2=$conn->prepare("SELECT cn_id FROM food_category_en WHERE catname= :catname_en");
    $stmt2->bindParam(':catname_en', $_POST['catname_en'], PDO::PARAM_STR);
    $stmt2->execute();
    $catresen= $stmt2->fetch(PDO::FETCH_ASSOC);
    if(count($catresen) > 0 ){
      $cn_id_en= $catresen['cn_id'];
    }else{
      $cn_id_en=0;
    }
  }
  if(empty($errors)){
  //pdo trasaction
$conn->beginTransaction();
try{

    if($cn_id == 0){
    $stmt1= $conn->prepare("INSERT INTO food_category (catname) VALUES (:catname)");
    $stmt1->bindParam(':catname', $_POST['catname'], PDO::PARAM_STR);
    $stmt1->execute();
    $cn_id= $conn->lastInsertId();
    echo "id1=" . $cn_id;
  }
 if($cn_id_en == 0){
    $stmt3= $conn->prepare("INSERT INTO food_category_en (catname)   VALUES (:catname_en)");
    $stmt3->bindParam(':catname_en', $_POST['catname_en'], PDO::PARAM_STR);
    $stmt3->execute();
    $cn_id_en= $conn->lastInsertId();
  }

  $stmt4=$conn->prepare("INSERT INTO food_category_main (st_id, cn_id, cn_id_en, catcount) VALUES (:st_id, :cn_id, :cn_id_en, :catcount)");
  $stmt4->bindParam(':st_id', $_POST['st_id'], PDO::PARAM_INT);
  $stmt4->bindParam(':cn_id', $cn_id, PDO::PARAM_INT);
  $stmt4->bindParam(':cn_id_en', $cn_id_en, PDO::PARAM_INT);
  $stmt4->bindParam(':catcount', $noc, PDO::PARAM_INT);
  $stmt4->execute();

$stmt5=$conn->prepare("UPDATE magazia_main SET noc= :noc WHERE st_id= :st_id");
$stmt5->bindParam(':st_id', $_SESSION['st_id'], PDO::PARAM_INT);
$stmt5->bindParam(':noc', $noc, PDO::PARAM_INT);
$stmt5->execute();

$conn->commit();
header ("Location:dashboard.php?store=".$_SESSION['name']);
exit;
 }catch(Exception $e){
//An exception has occured, which means that one of our database queries
//failed.
//Print out the error message.
echo $e->getMessage();
//Rollback the transaction.
$conn->rollBack();
}

i wont accept this as an answer for few days, in order to be seen from as many users is possible

score -1 · Answer 2 · edited Dec 24 '17 at 00:26

Please replace $stmt1, $stmt3 with $conn your code is like this

$conn->beginTransaction();
try{
  $stmt=$conn->prepare("SELECT cn_id FROM food_category WHERE catname= :catname");
  $stmt->bindParam(':catname', $_POST['catname'], PDO::PARAM_STR);
  $stmt->execute();
  $catres= $stmt->fetch(PDO::FETCH_ASSOC);
  if(count($catres) > 0 ){
    $cn_id= $catres['cn_id'];
  }else{
    $stmt1= $conn->prepare("INSERT INTO food_category (catname) VALUES (:catname)");
    $stmt1->bindParam(':catname', $_POST['catname'], PDO::PARAM_STR);
    $stmt1->execute();
    $cn_id= $conn->lastInsertId();
  }

  $stmt2=$conn->prepare("SELECT cn_id FROM food_category_en WHERE catname= :catname_en");
  $stmt2->bindParam(':catname_en', $_POST['catname_en'], PDO::PARAM_STR);
  $stmt2->execute();
  $catresen= $stmt2->fetch(PDO::FETCH_ASSOC);
  if(count($catresen) > 0 ){
    $cn_id_en= $catresen['cn_id'];
  }else{
    $stmt3= $conn->prepare("INSERT INTO food_category_en (catname) VALUES (:catname_en)");
    $stmt3->bindParam(':catname_en', $_POST['catname_en'], PDO::PARAM_STR);
    $stmt3->execute();
    $cn_id_en= $conn->lastInsertId();
  }

  $stmt4=$conn->prepare("INSERT INTO food_category_main (st_id, cn_id, cn_id_en, catcount) VALUES (:st_id, :cn_id, :cn_id_en, :catcount)");
  $stmt4->bindParam(':st_id', $_POST['st_id'], PDO::PARAM_INT);
  $stmt4->bindParam(':cn_id', $cn_id, PDO::PARAM_INT);
  $stmt4->bindParam(':cn_id_en', $cn_id_en, PDO::PARAM_INT);
  $stmt4->bindParam(':catcount', $noc, PDO::PARAM_INT);
  $stmt4->execute();

$stmt5=$conn->prepare("UPDATE magazia_main SET noc= :noc WHERE st_id= :st_id");
$stmt5->bindParam(':st_id', $_SESSION['st_id'], PDO::PARAM_INT);
$stmt5->bindParam(':noc', $noc, PDO::PARAM_INT);
$stmt5->execute();

$conn->commit();
header ("Location:dashboard.php?store=".$_SESSION['name']);
exit;
   }catch(Exception $e){
//An exception has occured, which means that one of our database queries
//failed.
//Print out the error message.
echo $e->getMessage();
//Rollback the transaction.
$conn->rollBack();

 }

If you get by SQL query then

$stmt1 = $db->query("SELECT LAST_INSERT_ID()");
$lastId = $stmt1->fetchColumn();

hello @sanjay paliwal and thanks for your answer, i tried this but the null result remains.. — vaggelis, Dec 24 '17 at 13:57

lastInsertId() returns NULL

2 Answers2

Linked