Transaction Wrapper

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’ve come across my first situation on a project that requires the usage of SQL transactions, I have a wrapper class for MySQLi in PHP that reduces and improves the readability of my code.

Here is the wrapper class for a transaction:

[COLOR=#0000BB]<?php
public function [COLOR=#0000BB]transaction() {
[COLOR=#0000BB]$count = [COLOR=#0000BB]func_num_args();

if ([COLOR=#0000BB]$count) {
[COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]autocommit([COLOR=#0000BB]false);

[COLOR=#0000BB]$return = [COLOR=#0000BB]false;

for ([COLOR=#0000BB]$i = [COLOR=#0000BB]0; [COLOR=#0000BB]$i < [COLOR=#0000BB]$count; [COLOR=#0000BB]$i++) {
if (([COLOR=#0000BB]$query = [COLOR=#0000BB]func_get_arg([COLOR=#0000BB]$i)) && ![COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]query([COLOR=#0000BB]$query)) {
[COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]rollback();
[COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]autocommit([COLOR=#0000BB]true);

return [COLOR=#0000BB]false;
}
}

if ([COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]commit()) [COLOR=#0000BB]$return = [COLOR=#0000BB]true;

else [COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]rollback();

[COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]autocommit([COLOR=#0000BB]true);

return [COLOR=#0000BB]$return;
}

else return [COLOR=#0000BB]false;
}
[COLOR=#0000BB]?>

I’ve been researching transactions and realised they are “all or nothing”, meaning I could change the following lines from:

[COLOR=#0000BB]<?php
for ([COLOR=#0000BB]$i = [COLOR=#0000BB]0; [COLOR=#0000BB]$i < [COLOR=#0000BB]$count; [COLOR=#0000BB]$i++) {
if (([COLOR=#0000BB]$query = [COLOR=#0000BB]func_get_arg([COLOR=#0000BB]$i)) && ![COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]query([COLOR=#0000BB]$query)) {
[COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]rollback();
[COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]autocommit([COLOR=#0000BB]true);

return [COLOR=#0000BB]false;
}
}
[COLOR=#0000BB]?>

to:

[COLOR=#0000BB]<?php for ([COLOR=#0000BB]$i = [COLOR=#0000BB]0; [COLOR=#0000BB]$i < [COLOR=#0000BB]$count; [COLOR=#0000BB]$i++) [COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]query([COLOR=#0000BB]$query); [COLOR=#0000BB]?>

And the outcome of the function would be the same (but better optimised and less code).

Would I be correct in assuming this?

Cheers!

2

After researching it a bit more I’ve come to the realisation a execution check on each query is not required as the trasnaction block is all-or-nothing resulting in the final function:

[COLOR=#0000BB]<?php
public function [COLOR=#0000BB]transaction() {
[COLOR=#0000BB]$count = [COLOR=#0000BB]func_num_args();

if ([COLOR=#0000BB]$count) {
[COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]autocommit([COLOR=#0000BB]false);

[COLOR=#0000BB]$return = [COLOR=#0000BB]false;

for ([COLOR=#0000BB]$i = [COLOR=#0000BB]0; [COLOR=#0000BB]$i < [COLOR=#0000BB]$count; [COLOR=#0000BB]$i++) if ([COLOR=#0000BB]$query = [COLOR=#0000BB]func_get_arg([COLOR=#0000BB]$i)) [COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]query([COLOR=#0000BB]$query);

if ([COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]commit()) [COLOR=#0000BB]$return = [COLOR=#0000BB]true;

else [COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]rollback();

[COLOR=#0000BB]self::[COLOR=#0000BB]$SQL->[COLOR=#0000BB]autocommit([COLOR=#0000BB]true);

return [COLOR=#0000BB]$return;
}

else return [COLOR=#0000BB]false;
}
[COLOR=#0000BB]?>