What is the correct way of doing transactions

Question

I am trying to do multiple queries in one call, so I organized my code as below:

 try{
    $query = $this->conn->begin_transaction();
    
    $query = $this->conn->prepare("INSERT INTO table1(xx, xxx, xxxx, xxxxx, xxxxxx, xxxxxxx, xxxxxxxx, xxxxxxxxx, xxxxxxxxxx, xxxxxxxxxxx, xxxxxxxxxxx) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    $query->bind_param("sssssssssss", $var1, $var2, $var3, $var4, $var5, $var6, $var7, $var8,  $var8,  $var9, $var10);                                 
    $result1 = $query->execute();
    
    $var1= $xxx- $xxxx;
    $query = $this->conn->prepare("UPDATE table2 set xxx = ? WHERE xxxx = ?");
    $query->bind_param("ss", $var1, $var2);  
    $result2 = $query->execute(); 
     
    $query = $this->conn->prepare("INSERT INTO table3 (xx, xxx, xxxx, xxxxx, xxxxxx, xxxxxxx) 
    VALUES (?, ?, ?, ?, ?, ?) ");
    $query->bind_param("ssssss", $var1, $var2, $var3, $var4, $var5, $var6);    
    $result3 = $query->execute(); 
    
    $query = $this->conn->commit();

    if($result1 == true AND $result2 == true AND $result3 == TRUE){
        return "Success";
    }
    exit();   
}catch (Exception $e){
    $this->conn->rollback(); 
    throw $e;   
}

Not sure if this is the correct way of doing it, but I don't want to insert/update records in the database if all queries are not true. It would help me a lot if someone can go through the code and tell me his thoughts, this works fine but I am not sure if this is a good approach.

@Dharman according to your link, my code is wrong? Does that means if I want to use commit() I should not use execute() before ? — ron9, Oct 28 '20 at 11:06
I didn't say your code is wrong. I assumed that since you are asking on Stack Overflow you don't know how to use transactions. Maybe you have not enabled mysqli error reporting. If the code works, but you are requesting review then you should ask at [codereview.se] — Dharman, Oct 28 '20 at 11:08
@Dharman the important part in that post is missing. it will proceed with the second query if the first one fails — Your Common Sense, Oct 28 '20 at 11:10
ah, there is your post there. but man. how many readers would make it there... Stack overflow at its best — Your Common Sense, Oct 28 '20 at 11:11
@YourCommonSense The problem was the manual. I changed the example in https://www.php.net/manual/en/mysqli.begin-transaction.php hopefully it is now better. — Dharman, Oct 31 '20 at 11:26

What is the correct way of doing transactions

0 Answers0