|
|
Posted by Jerry Stuckle on 08/01/07 14:57
João Morais wrote:
> Hi there guys,
>
> 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));
>
> 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?
>
> If I copy-paste it and type it on MySQL console or PHPMyAdmin, it will
> run with no problems.
>
> 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?
>
> This are maybe simple questions, but thats the first time Im using
> transactions, and I didn't found much info on this.
>
> PS: My database is running, and has some data in it.
>
> Thanks for your attention.
>
You can't do it with mysql_query(), but if you use the improved MySQL
interface you can use mysqli_multi_query (or mysqli->multi_query()).
However, never having tried it (I don't like multiple queries), I'm not
sure what would happen if one of the queries failed. I agree with Rik -
it's better to use four separate queries and check the results before
rolling back or committing.
And I never claimed this wasn't a PHP question :-)
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|