0

I have the following query which does not work when it is initiated by my PHP code:

$sql = 'START TRANSACTION;
        DELETE FROM task_actions
            WHERE task_id='.$id.';
        DELETE FROM tasks
            WHERE id='.$id.';
        COMMIT;
       ';

When I echo $sql and put the output directly into phpMyAdmin, it works without a problem; and when I had it done in two steps instead of one transaction, it worked from my PHP code, too.

I first thought MySQL might not allow transactions, but stackoverflow.com/questions/2050310 and stackoverflow.com/questions/2960012 showed that was wrong.

I found I could disable autocommit, do both queries and reactivate autocommit (stackoverflow.com/a/17607619 & stackoverflow.com/a/12092151), but I would prefer not to.

Any ideas why it does not work?

Community
  • 1
  • 1
Pascal
  • 453
  • 2
  • 13
  • 1
    I am not overly familiar with mysqli, but are you using [multi-query](http://php.net/manual/en/mysqli.multi-query.php) to run these multiple queries at once? From memory, most connections will bork if you try to run multiple queries. – Fluffeh Oct 29 '13 at 21:26
  • I was not. You are right and it seems quite logical now I've got the answer and your comment. I'll go back to my original two step implementation - seems shorter and simpler in this case. – Pascal Nov 07 '13 at 12:06

1 Answers1

1
$sql = 'START TRANSACTION';
// run this query
$sql = 'DELETE FROM task_actions WHERE task_id=?';
// run this query
$sql = 'DELETE FROM tasks WHERE id=?';
// run this query
$sql = 'COMMIT';
// finally run this one
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345