Posted by Rik on 08/01/07 12:57

On Wed, 01 Aug 2007 14:44:04 +0200, João Morais <jcsmorais@gmail.com>
wrote:
> My doubt is related with MySQL and Transactions (InnoDB) so here it
> goes:
>
> I have a query like this:
>
> $query = 'START TRANSACTION; ';
> $query .= 'UPDATE sections ';
> $query .= 'SET position=position-%d ';
> $query .= 'WHERE position > %d; ';
> $query .= 'DELETE FROM sections ';
> $query .= 'WHERE id IN %s; ';
> $query .= 'COMMIT;';
>
> $result = $db->query(sprintf($query, count($id), $db-
>> result($result), $ids));

Before anyone starts complaining, this _is_ imho a PHP question :P.

> This will perform a query similar to this one:
>
> START TRANSACTION; UPDATE sections SET position=position-2 WHERE
> position > 1; DELETE FROM sections WHERE id IN (15,16); COMMIT;
>
> If I var_dimp $result I will get: bool(false)
>
> So query failed, my doubt is why?

From the manual:
mysql_query() sends an unique query (multiple queries are not supported)

> If I copy-paste it and type it on MySQL console or PHPMyAdmin, it will
> run with no problems.

Indeed.

>
> Also If I separate $query in four queries, and run them separately,
> the script will run too.
>
> Can't this be done like this? If so, what is the correct way of doing
> it?

In seperate queries, as you indicate :P. Then again, the main plus of
transactions is IMO the rollback, so test for results and rollback as
needed, which would mean getting the result of the seperate queries,
--
Rik Wasmus

[Back to original message]