Using transactions in MySQL + PHP

[=?iso-8859-1?B?Sm/jbyBNb3JhaXM=?=]

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=positi on-%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=positi on-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.

[=?iso-8859-1?B?Sm/jbyBNb3JhaXM=?=]

So query failed, my doubt is why?

After reading PHP manual, I know now that multiple queries are not
suported..

Also If I separate $query in four queries, and run them separately,
the script will run too.

Is this correct? The only way? How should it be done

[Rik]

On Wed, 01 Aug 2007 14:44:04 +0200, João Morais <jc*******@gmai l.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=positi on-%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=positi on-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

[Rik]

On Wed, 01 Aug 2007 14:52:13 +0200, João Morais <jc*******@gmai l.com>
wrote:

>So query failed, my doubt is why?

After reading PHP manual, I know now that multiple queries are not
suported..

>Also If I separate $query in four queries, and run them separately,
the script will run too.

Is this correct?

Yes.

The only way?

Nope, but the most usefull one.

How should it be done

Seperate queries, check the return, commit or rollback depending on the
result.
--
Rik Wasmus

[Jerry Stuckle]

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=positi on-%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=positi on-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_qu ery (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.
js*******@attgl obal.net
=============== ===

[ELINTPimp]

On Aug 1, 10:57 am, Jerry Stuckle <jstuck...@attg lobal.netwrote:

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=positi on-%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=positi on-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_qu ery (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.
jstuck...@attgl obal.net
=============== ===

Personally, I use pdo_mysql.

$query = 'START TRANSACTION; ';
$query .= 'UPDATE sections ';
$query .= 'SET position=positi on-%d ';
$query .= 'WHERE position %d; ';
$query .= 'DELETE FROM sections ';
$query .= 'WHERE id IN %s; ';
$query .= 'COMMIT;';

becomes

try {
$db = new PDO($dns, $user, $pass)
$db->beginTransacti on();
$up = $db->prepare("UPDAT E sections SET position=? WHERE position

?");

$up->bindParam(1, '$count($id)');
$up->bindParam(2, '$db->result($result )');
$up->execute();
$dl = $db->prepare("delet e from sections where id in ?");
$dl->bindParam(1, '$ids');
$dl->execute();
}
catch (PDOException $e) {
$db->rollback();
echo "Woops: ".$e->getMessage;
}

you probably will want to do something different with you error
handling, but i just wanted to add a rollback as Rik pointed out...is
really the big plus to transactions.

Steve

[=?iso-8859-1?B?Sm/jbyBNb3JhaXM=?=]

First, thanks to all for your answers.
Second, lets see..
$query1 = 'START TRANSACTION';
$result1 = $db->query($query1) ;
if (!$result1) die("transactio n failed at step1");

$query2 = 'UPDATE sections SET position=positi on-%d WHERE position >
%d';
$result2 = $db->query(sprintf( $query2, count($id), $db-

>result($result )));

if (!$result2) die("transactio n failed at step2");

$query3 = 'DELETE FROM sections WHERE id IN %s';
$result3 = $db->query(sprintf( $query3, $ids));
if (!$result3) die("transactio n failed at step3");

$query4 = 'COMMIT';
$result4 = $db->query($query4) ;
if (!$result4) die("transactio n 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.

[Jerry Stuckle]

João Morais wrote:

First, thanks to all for your answers.
Second, lets see..
$query1 = 'START TRANSACTION';
$result1 = $db->query($query1) ;
if (!$result1) die("transactio n failed at step1");

$query2 = 'UPDATE sections SET position=positi on-%d WHERE position >
%d';
$result2 = $db->query(sprintf( $query2, count($id), $db-

>result($result )));

if (!$result2) die("transactio n failed at step2");

$query3 = 'DELETE FROM sections WHERE id IN %s';
$result3 = $db->query(sprintf( $query3, $ids));
if (!$result3) die("transactio n failed at step3");

$query4 = 'COMMIT';
$result4 = $db->query($query4) ;
if (!$result4) die("transactio n 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=positi on-%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('ROLLBAC K');
echo "COMMIT failed";
}
}
else {
$db->query('ROLLBAC K');
echo "DELETE failed";
}
}
else {
$db->query('ROLLBAC K');
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.
js*******@attgl obal.net
=============== ===

[=?iso-8859-1?B?Sm/jbyBNb3JhaXM=?=]

How to use COMMIT/ROLLBACK is more of a SQL question and belongs in

comp.lang.mysql .

Sorry but I dont agree.. the question is not how to use commit/
rollback in a general way, but with PHP. If I only used MySQL i
wouldnt have this problem at all.

I don't think it's a good idea to use die() - is that what you want your
client to see?

I just used die as an example of abort/report failure, I wont use this
in my final script.

>Additionally , I'm not sure die() will roll back your transaction.

For what I understood, (and I may be wrong), the data will only be
saved if COMMIT is done. So if one of the first 3 queries fail, no
COMMIT will be done, then no data will be saved.

Same occurs if first 3 queries are made with success, and the last one
fails.

There are other things you will need to consider with COMMIT and
ROLLBACK.

What things?

[Jerry Stuckle]

João Morais wrote:

>How to use COMMIT/ROLLBACK is more of a SQL question and belongs in
comp.lang.mysq l.

Sorry but I dont agree.. the question is not how to use commit/
rollback in a general way, but with PHP. If I only used MySQL i
wouldnt have this problem at all.

Commit/Rollback are SQL commands, not PHP. If you need to learn more
about how to use them, you need a SQL newsgroup. Their use is not
dependent on PHP or any other language. They work the same from the
MySQL command line, for instance.

mysql_query(), etc. are PHP functions, and are appropriate for this group.

>I don't think it's a good idea to use die() - is that what you want your
client to see?

I just used die as an example of abort/report failure, I wont use this
in my final script.

>Additionally , I'm not sure die() will roll back your transaction.

For what I understood, (and I may be wrong), the data will only be
saved if COMMIT is done. So if one of the first 3 queries fail, no
COMMIT will be done, then no data will be saved.

Same occurs if first 3 queries are made with success, and the last one
fails.

As I said - I'm not sure on that. You need to check the MySQL
newsgroups as to what MySQL does in this case.

>

>There are other things you will need to consider with COMMIT and
ROLLBACK.

What things?

See comp.databases. mysql.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===