Reputation: 422
I seem to be having trouble with the rollback feature in PHP7. Here's my code, but if I supply the queries and put an error into one of them, it won't roll back. It still commits everything that had been completed.
function sql_transaction(...$queries) {
global $db;
$success = 1;
$db->autocommit(FALSE);
foreach($queries as $query) {
$result = $db->query($query);
echo $result;
if(!$result) {
$success = 0;
break;
}
}
if($success == 1) {
$db->commit();
}else{
$db->rollback();
}
$db->autocommit(TRUE);
return $success;
}
Upvotes: 0
Views: 3490
Reputation: 19372
You've to start transaction before doing this.
Because You must tell to database that You're going to start transaction.
You've to put: $db->begin_transaction();
after autocommit(FALSE);
Please read documentation: mysqli::begin_transaction
P.S. Please keep in mind it cannot be done with tables that engine does not support transactions. So if after adding begin_transaction
statement rollback()
did not work, check Your tables engine that it's set to engine with transaction support.
To check Your tables engine call query in mysql terminal:
SHOW TABLE STATUS FROM database_name_goes_here;
You'll get list of tables in Your database with engines defined.
To get list of transaction-safe engines You can do by calling query in mysql terminal (find Transactions: YES):
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
...
Upvotes: 3