0

I am wanting to some transactional mysql in my PHP, I have had a look at the PHP docs and there are a couple of functions I need to use, 

mysqli_begin_transaction
mysqli_rollback
mysqli_commit

My code currently looks like this, 

mysqli_begin_transaction($db_link, MYSQLI_TRANS_START_READ_WRITE);  
$sql = "SELECT * FROM table";  
$result = mysqli_query($sql);  
if(!$result){
    $rollback = true;  
}
$sql = "SELECT * FROM another";
$result = mysqli_query($sql);  
if(!$result){
    $rollback = true;
}
$sql = "DELETE FROM table_name WHERE condition;"
mysqli_query($sql);
if(mysqli_affected_rows($db_link) < 0){
    $rollback = true;
}
if($rollback){
    mysqli_rollback($db_link)
} else {
    mysqli_commit($db_link)
}

This is very rough pseudo code, but my question is that the transaction function all return values according to the php documentation so should I be wrapping them in conditional statements and throwing an exception of something similar if they dont return true.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Udders
  • 6,914
  • 24
  • 102
  • 194
  • 3
    WHY are you setting `$rollback = true;` after a failed SELECT, nothing is changed by a select – RiggsFolly Nov 15 '18 at 14:26
  • 1
    Maybe your actual code is significantly different (in which case, you should really be showing _that_), but what you have here really isn't the best use-case for transactions. – Patrick Q Nov 15 '18 at 14:28
  • Please also take a look at https://secure.php.net/manual/en/mysqli.autocommit.php – Lithilion Nov 15 '18 at 14:30
  • 3
    You would normally use a transaction to maintain database integrity, which normally means you would be updating more than one table, in for example a parent-child relationship. So for example Create Invoice, create 3 invoice line items. You wrap all 4 inserts in a transaction to make sure that if one insert fails, you dont end up with a Invoice and only one line item – RiggsFolly Nov 15 '18 at 14:31
  • My code is very different but Im under non disclosure so not willing to post it. The case remains should I be doing `if(mysqli_rollback($dblink)) {}` – Udders Nov 15 '18 at 14:42
  • Well in this senario, as you have only ONE DELETE, it will either work or fail. If it failes there is nothing to Rollback, if it works, there was little point in starting a transaction as without one, a working delete will delete – RiggsFolly Nov 15 '18 at 14:44
  • 2
    Basically, this senario is NOT a good example of when, why and how you would use transactions – RiggsFolly Nov 15 '18 at 14:45
  • We can't advise you on code we can't see. I'll say this though, do you know what you would do with the information of whether or not the functions returned `true`? If you wouldn't take meaningful action on that, then there's really no point, is there? – Patrick Q Nov 15 '18 at 14:50

0 Answers0