0

The scenario is I have two table, we can call it table_a and table_b. one have 4 column, and the other one has 3, here is what they look like

table_a                        table_b
+------+------+-----+----+       +-----+-----+-----+
| a_id | name | age |sex |       |b_id |a_id |type |
+------+------+-----+----+       +-----+-----+-----+

I have a query that insert a data to the two table at the same time

$query = "INSERT INTO table_a SET name=:name, age=:age, sex=:sex; INSERT INTO table_b SET   type=:type";
$stmt = $this->conn->prepare($query);
$this->name = $this->name;
$this->name = $this->age;
$this->name = $this->sex;
$this->name = $this->type;
$stmt->bindParam(':name', $this->name);
$stmt->bindParam(':age', $this->age);
$stmt->bindParam(':sex', $this->sex);
$stmt->bindParam(':type', $this->type);
if($stmt->execute()){
   return $this->conn->lastInsertId();
 }else{ 
   return -1;}

a_id on table_a is autoincrement similar to b_id in table_b, name, age, sex, type were all inserted by user using 

$_POST

Now, my inquiry, is there a way I can automatically put a_id value from table_a to a_id in table_b? The lastInsertId is working properly, though Im not sure how I can put it on the other table. I am also thinking about another solution, and that is assigning a value to a_id using this 

a_id=:LAST_INSERT_ID()

But I am not sure how to do that or if it is possible.

aaa28
  • 81
  • 8
  • 1
    You may want to take a look at [this](https://stackoverflow.com/a/5178713/4835201). – Coldark Mar 01 '20 at 03:23
  • You can just remove from second table requirement `increment` an add a id from the first table in to the second one. That will give you the consistence of the records inserted. – Serghei Leonenco Mar 01 '20 at 03:23
  • @Coldark nice example – Serghei Leonenco Mar 01 '20 at 03:24
  • @SergheiLeonenco I am not sure I follow your suggestion, can you further elaborate – aaa28 Mar 01 '20 at 03:29
  • If you remove on the second (backup) table option `Auto Increment` then you will be able to store any data record with provided `id` without any conflict associated with the main table – Serghei Leonenco Mar 01 '20 at 03:31
  • @Coldark I actually already read that and follow that instruction, but the lastInsertId() in that process return 0 value. The code I am using to insert data to two table is working, believe it or not, and I also able to retrieve the lastInsertId() from table_a or the first table. What I dont know is how can I be able to Insert the retrieved lastInsertId() from table_a to a_id column in table_b. – aaa28 Mar 01 '20 at 03:33
  • @SergheiLeonenco But how can I be able to do that automatically after running the first query. Because if I understand it correctly, what you are suggesting is I should run another query, manually, to insert the ID from table_a to table_b, probably using a form. I am thinking to INSERT the ID to the second table using a simple query like UPDATE table_b SET a_id=:aid" or SET a_id:=LAST_INSERT_ID() – aaa28 Mar 01 '20 at 03:47
  • You can just edit your first query to proposed one. You should not add anything else. Just run this in SQL IDE and you will see that both tables are updated. – Serghei Leonenco Mar 01 '20 at 03:49

1 Answers1

0

Try this:

INSERT INTO table_a SET name=:name, age=:age, sex=:sex; INSERT INTO table_b SET a_id=LAST_INSERT_ID(), type=:type;
Serghei Leonenco
  • 3,478
  • 2
  • 8
  • 16