I am having some issues with a site i wrote quite sometime ago, after looking into it, every now and then (6 times out off over 5,000 inserts) some inserts aren't being done.. EG:
mysql_query("INSERT INTO `invoices` (subtotal, vat, total) VALUES ('30.00', '6.00', '36.00');", $conn);
$invoice_id = mysql_insert_id();
for($i=0; $i < 3; $i++) mysql_query("INSERT INTO `invoice_items` (item_name, invoice_id, subtotal, vat, total, tax_code) VALUES ('Bricks', '$invoice_id', '10.00', '2.00', '12.00', 'T1');", $conn);
Sometimes the ones in the for loop don't get inserted, obviously the for loop is a foreach which loops through an array in the real code..
I'm guessing because the connection gets interrupted half way through..
I want to ensure everything gets inserted or nothing, Should i switch to using transactions?
Dean.
Try to add at the end of the query: or die ( mysql_error() ); so you can check what errors you've get.
And if you're really making 5000 inserts you can set the timeout to a large number so it never stopt by accident. Or else you can let it check if everything is inserted in the table and if not, it inserts the skipped data...
Since your not doing this inside a transaction you can get half inserts. Each insert can fail for a lot of different reasons. Duplicate key exceptions, lock timeout and so on.
If all these inserts are to be treated as a single unit add a transaction around it (it could possibly also speed up the insert as you get fever transactions)