how to use lastinsertid, LAST_INSERT_ID to insert record into two table (PHP MYSQL)

Question

I have two table, in table 1, there is an auto increment ID, I wanted to INSERT that last ID into table 2 right after the record was saved into table 1. Is it possible to do that. I have a form that the user fill up, the data collected was then submitted to two different table upon submission. This is my code for INSERTing the data

class.php

public function insertID(){
  $query = "INSERT INTO table_1 SET book_name=:book_name; INSERT INTO table_2 SET 
book_type=:book_type, book_type_id=:book_type_id";

 $stmt = $this->conn->prepare($query);

 $this->book_name=$this->name;  //this data came from a form
 $this->book_type=$this->book_type; //same as this one
 $this->book_type_id=(Im not sure what to put here)(I tried LAST_INSERT_ID and 
 mysqli_insert_id) 
 //data the will go here should come from the last inserted ID so I connect table 1 and 
 table 2 using this ID

  $stmt->bindParam(':book_name', $this->book_name);
  $stmt->bindParam(':book_type', $this->book_type);
  $stmt->bindParam(':book_type_id', $this->book_type_id);

  if($stmt->execute()){
        return  true;
    }else{
        return false;
    }
 }

here is my form

 include_once 'class.php';
 $class = new Class($db);

 if($_POST){
     $class->book_name=$_POST['book_name'];
     $class->book_type=$_POST['book_type'];

  if($class->insertId()){
    echo "saved";
   }else {
     not saved
    }
  }

any suggestion would be greatly appreciated thank you.

Here is my solution with the help from @GMB

class.php

public function insertID(){

 $query_1 = "INSERT INTO table_1
     SET book_name=:book_name";

     $stmt_1 = $this->conn->prepare($query_1);         

     $this->book_name=$this->book_name; 

     $stmt_1->bindParam(':book_name', $this->book_name);

     if($stmt_1->execute()){

           $query_2 = "INSERT INTO table_2 
            SET book_type=:book_type, book_type_id=:book_type_id";

            $stmt_2 = $this->conn->prepare($query_2);

            $this->book_type=$this->book_type;
            $this->book_type_id=$this->conn->lastInsertId();

            $stmt_2->bindParam(':book_type', $this->book_type);
            $stmt_2->bindParam(':book_type_id', $this->book_type_id);

                  if($stmt_2->execute()){
                     return  true;
                         }else{
                           return false;
                                 }
    }else{
        return false;
    }
 }

[Didn't you ask this already?](https://stackoverflow.com/questions/60471659/possible-way-to-insert-lastinsertid-from-one-table-to-another-at-the-same-time-o) — Funk Forty Niner, Mar 03 '20 at 21:54

GMB · Answer 1 · 2020-03-03T22:10:27.933

1

You can do this with two separate queries:

$query1 = "INSERT INTO table_1(book_name) VALUES(:book_name)";
$query2 = "INSERT INTO table_2(book_type, book_type_id) VALUES(:book_type, LAST_INSERT_ID())";

The first query inserts in the parent table. The seconds uses MySQL function LAST_INSERT_ID() to recover the id generated during the previous insert, and insert it in the child table.

You need to run each query independently in a separate database call. Something like:

$query1 = "INSERT INTO table_1(book_name) VALUES(:book_name)";
$stmt1 = $this->conn->prepare($query1);
$stmt1->bindParam(':book_name', ...);
if(!$stmt1->execute()){ # error handling}

$query2 = "INSERT INTO table_2(book_type, book_type_id) VALUES(:book_type, LAST_INSERT_ID())";
$stmt2 = $this->conn->prepare($query2);
$stmt1->bindParam(':book_type', ...);
if(!$stmt1->execute()){ # error handling }

edited Mar 03 '20 at 22:10

answered Mar 03 '20 at 21:52

GMB

216,147
25
84
135

how would I integrate that in my function? – aaa28 Mar 03 '20 at 21:54
Is it possible to do this with one transaction from the user? Im confused, but I am trying really hard to get what youre suggesting. – aaa28 Mar 03 '20 at 21:59
@aaa28: I added some (pseudo) code to my answer. – GMB Mar 03 '20 at 22:13
thats generous of you, I have to make two forms then, not excatly what I am going for, but I guess this will do for now, thank you – aaa28 Mar 03 '20 at 22:17
@aaa28: you don't have to make two forms. You just need to execute two SQL queries when your one form is submitted. – GMB Mar 03 '20 at 22:19
I am not sure if I did it correctly , but it only saved on table one. – aaa28 Mar 03 '20 at 22:44
ok now it saved on the other table , but still the LAST_INSERT _ID is not working. – aaa28 Mar 03 '20 at 22:49
it is working now, a very big thanks to you @GMB, I use $this->conn->lastInsertId(); – aaa28 Mar 03 '20 at 22:53
I want to edit my post to share my solution,but I do not know how, because there are a lot of tweaks from the original code and the answer code. – aaa28 Mar 03 '20 at 22:55

how to use lastinsertid, LAST_INSERT_ID to insert record into two table (PHP MYSQL)

1 Answers1