MySQL Forums
Forum List  »  PHP

Use Transaction Blocks or Multiple PHP mysqli_query()
Posted by: Arthur Chan
Date: December 13, 2018 12:47AM

QUESTION:
Can you share experience in multiple, concurrent inserts using either PHP or mySQL sprocs please?

PROBLEM:
I need to insert into multiple tables to guarantee referential integrity because there are intersections between base tables e.g. a simple example

Parts ||--< PartOrders >--|| Orders


I have two approaches:

111) In PHP, I can use multiple mysqli_queries like this
set autocommit = 0;
$orders = "call sp_addNewOrders(...)";
$orderparts = "call sp_addNewOrderParts(...)";
if($orders && $orderparts) {
mysqli_query($con,"COMMIT");
} else { mysqli_query($con,"ROLLBACK"); }

222) In sproc, I can use a transaction block. I simply upload from Java all the relevant data and stuff them into the param list.

The reason I am hesitant to use PHP is b/c in the past, I had difficulties with multiple mysqli_queries, having as well to count the rows returned ( 0 or not 0) and I sometimes couldn't get a true or false on whether the query was executed successfully (! had a null returned from the mysqli_query)

Thanks in advance for sharing your experience.

Options: ReplyQuote


Subject
Written By
Posted
Use Transaction Blocks or Multiple PHP mysqli_query()
December 13, 2018 12:47AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.