|
|
Posted by Jerry Stuckle on 08/01/07 22:35
João Morais wrote:
> First, thanks to all for your answers.
> Second, lets see..
> $query1 = 'START TRANSACTION';
> $result1 = $db->query($query1);
> if (!$result1) die("transaction failed at step1");
>
> $query2 = 'UPDATE sections SET position=position-%d WHERE position >
> %d';
> $result2 = $db->query(sprintf($query2, count($id), $db-
>> result($result)));
> if (!$result2) die("transaction failed at step2");
>
> $query3 = 'DELETE FROM sections WHERE id IN %s';
> $result3 = $db->query(sprintf($query3, $ids));
> if (!$result3) die("transaction failed at step3");
>
> $query4 = 'COMMIT';
> $result4 = $db->query($query4);
> if (!$result4) die("transaction failed at step4");
> echo 'Done...';
>
> Logic, split main query into 4 queries.
> Check one by one if they are made with success, if so proceed,
> otherwise report failure.
> Since data will only be saved if COMMIT is done, shoud step4 be like
> the one above? If one of the first 3 steps fail, script will be
> aborted and no data will be commited.
> Im not sure this is the right way to use commit/rollback .. any ideias/
> comments on this will be appreciated.
>
> Thanks in advance guys.
>
How to use COMMIT/ROLLBACK is more of a SQL question and belongs in
comp.lang.mysql.
I don't think it's a good idea to use die() - is that what you want your
client to see? Additionally, I'm not sure die() will roll back your
transaction. Rather, you should handle it more gracefully, i.e.
(not tested)
$query1 = 'START TRANSACTION';
$result1 = $db->query($query1);
if ($result1) {
$query2 =
'UPDATE sections SET position=position-%d WHERE position > %d';
$result2 = $db->query(sprintf($query2, count($id),
$db-result($result)));
if ($result2) {
$query3 = 'DELETE FROM sections WHERE id IN %s';
$result3 = $db->query(sprintf($query3, $ids));
if ($result3) {
$query4 = 'COMMIT';
$result4 = $db->query($query4);
if ($result4) {
echo 'Done...';
}
else {
$db->query('ROLLBACK');
echo "COMMIT failed";
}
}
else {
$db->query('ROLLBACK');
echo "DELETE failed";
}
}
else {
$db->query('ROLLBACK');
echo "UPDATE failed";
}
}
else {
echo "START TRANSACTION failed";
}
There are shorter ways to do it, but I tried to keep some semblance of
your code.
There are other things you will need to consider with COMMIT and
ROLLBACK. If you're not familiar with them, you need to discuss the
subject in a MySQL newsgroup such as comp.databases.mysql.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|