Inserting data to two tables at once in mysql

Question

I have two mysql tables:

table1_id (PK) | table1_data

and

table2_id (PK) | table1_id (FK) | table2_data

and they both together have to be created at once to represent single object. Primary keys are auto incremented, so after inserting table1_data when I move on to creating table2, how can I get the correct, freshly created table1_id value to insert there?

I'm doing it in php:

$stmt = $conn->prepare("INSERT INTO table1 (`table1_data`) VALUES (:data)"); $stmt->bindParam(':data', $data); $stmt->execute(); $stmt2 = $conn->prepare("INSERT INTO table2 (`table1_id`,`table2_data`) VALUES (:id,:data)"); $stmt2->bindParam(':id', $table1_id); //how can I get the id of row just created above by stmt? $stmt2->bindParam(':data', $data2); $stmt2->execute();

Oldskool · Accepted Answer · 2015-04-12 10:19:45Z

You can use the PDO::lastInsertId method for that. Call it after executing your first insert, so you get:

$stmt = $conn->prepare("INSERT INTO table1 (`table1_data`) VALUES (:data)"); $stmt->bindParam(':data', $data); $stmt->execute(); $table1_id = $conn->lastInsertId();

You can then use $table1_id in your second query.

Collectives™ on Stack Overflow

Inserting data to two tables at once in mysql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related