[RESOLVED] Inserting into multiple tables one query

[cluelessPHP]

$sql = "INSERT INTO img_upload (`id`, `img_name`, `img_path`, `img_type`)
	VALUES ('$id', '$filename','$filepath','$filetype') ;

INSERT INTO ads (`img_id`, `groupList`, `adType`, `dateField`, `adCost`)
VALUES (LAST_INSERT_ID(), '4', '$adType', CURDATE(), '$adCosts');

INSERT INTO admin (`id`, `ad_id`, `adType`)
VALUES ('$id',LAST_INSERT_ID(), '$adType');
		";

Although this insert works, I'm curious if there is a more elegant way of inserting into multiple tables at once, id been the main unique identifier of the database

[sneakyimp]

By default, MySQL won't run more than one query in a single statement. I think it'll run the first query but ignore the others. You can get around this with a little extra effort.

As for inserting data into multiple tables, it might be possible, but it sounds pretty tricky to me, possibly involving stored procedures or on-insert triggers or something.

If you want to make your inserts "atomic," i.e., no one messes with the affected tables between your first insert and your last, then perhaps you could START TRANSACTION first and then COMMIT after your last insert? See here for info on how transactions work in MySQL.

[cluelessPHP]

Thanks, I hadn't heard of TRANSACTION before, I'll look into that now

[Bonesnap]

I second the use of transactions.