CODEWITHSUNDEEP
phpmysqlinserttransactions
jakubplus
jakubplus

Reputation: 317

Multiple insert with last_insert_id in a transaction

I've got a series of records to insert to a database, but the structure of database requires a special approach:

BEGIN;
table contact -> firstname,lastname
table company -> name
table contact_company_vs -> contact_id,company_id (THESE TWO ARE LAST_INSERT_IDs FROM first two inserts)
COMMIT;

How to do that? Should I limit myself to php capabilities of storing variables by doing a few inserts one after another?

Upvotes: 0

Views: 423

Answers (1)

Iłya Bursov
Iłya Bursov

Reputation: 24146

actually you can do this either with php or with sql

PHP

  1. use mysqli/pdo and innodb tables
  2. set autocommit to off
  3. insert 1 -> save $id (you can get it via mysqli_insert_id and it will work properly inside transaction)
  4. insert 2 -> save $id2
  5. insert 3 -> you have $id and $id2
  6. commit all changes
  7. if any errors during logic - rollback them

SQL

I prefer to use stored procedures:

declare id1 bigint default 0;
declare id2 bigint default 0;
insert1 ... ;
SELECT LAST_INSERT_ID() into id1;
insert2 ... ;
SELECT LAST_INSERT_ID() into id2;
insert into contact_company_vs values(id1, id2);
select id1, id2;

such stored proc will even return both generated ids back into your logic

Upvotes: 1

Related Questions