CODEWITHSUNDEEP
phpmysql
randomuser2
randomuser2

Reputation: 105

Inserting data to two tables at once in mysql

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();

Upvotes: 0

Views: 53

Answers (1)

Oldskool
Oldskool

Reputation: 34837

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.

Upvotes: 1

Related Questions