MySQL Transaction From PHP

Question

QUERY 1:

$link = mysql_connect('localhost', 'root', '');
mysql_select_db('rems', $link);
mysql_query('SET AUTOCOMMIT=0; START TRANSACTION', $link);
mysql_query('DELETE FROM admins WHERE admin_id=4', $link);
mysql_query('ROLLBACK; SET AUTOCOMMIT=1', $link);

QUERY 2:

$link = mysql_connect('localhost', 'root', '');
mysql_select_db('rems', $link);
mysql_query('SET AUTOCOMMIT=0;START TRANSACTION;
    DELETE FROM admins WHERE admin_id=4;
    ROLLBACK; SET AUTOCOMMIT=1', $link);

From the above two queries the first one does not execute properly (transaction does not work) because of executing the queries separately by calling the mysql_query functions multiple times. But i need it do be done by this way. That is i need the result by the first way (calling mysql_query function several times for a single transaction)

Any IDEA please???

Why the requirement of autocommit & start in 1 query? Seems silly, and moreover: impossible with the standard mysql module. — Wrikken
– Wrikken, Commented Jul 19, 2010 at 11:27
And the ROLLBACK and SET AUTOCOMMIT=1 in the last statement seem similarly ill-advised. When you say it doesn't work, what exactly is happening? Are you getting an error? Or is nothing happening? Note that since you are rolling back the transaction, you expect nothing to happen. — Brian Hooper
– Brian Hooper, Commented Jul 19, 2010 at 11:29
To make sure: Do you use InnoDB as database engine? Only this supports transactions. — Felix Kling
– Felix Kling, Commented Jul 19, 2010 at 11:31
@Wrikken & Hooper: The idea of the technique of setting AUTOCOMMIT at the begining and ending of the query has been collected from the great PHP Framework CodeIgniter. @Hooper: As the transaction has ROLLED BACK so the QUERY should not affect as the result. But the row deleted if i use QUERY1 method and not delete if i use QUERY2. @Kling: I am using InnoDB. If i was not then no one of the above would work. But QUERY2 is working properly :) — A.N.M. Saiful Islam
– A.N.M. Saiful Islam, Commented Jul 19, 2010 at 11:49
QUERY2 does NOT work properly: the ENTIRE query FAILS (including your DELETE statement). Hence no actual delete, but that has nothing to do with a ROLLBACK, but a general error (try it: COMMIT there won't work) — Wrikken
– Wrikken, Commented Jul 19, 2010 at 12:51

Wrikken · Accepted Answer · 2010-07-19 12:15:19Z

5

With the standard mysql module, either call every query seperately:

mysql_query('SET AUTOCOMMIT=0');
mysql_query('START TRANSACTION');
mysql_query('DELETE FROM admins WHERE admin_id=4');
mysql_query('ROLLBACK');//nothing will be done, I assume it's for testing
mysql_query('SET AUTOCOMMIT=1');

Or create a procedure first:

DELIMITER //
CREATE PROCEDURE weirdrolledbackdelete (IN oid INTEGER)
BEGIN
SET AUTOCOMMIT=0;
START TRANSACTION;
DELETE FROM admins WHERE id = oid;
ROLLBACK;
SET AUTOCOMMIT=1;
END;//

So you can use it later:

mysql_query('CALL weirdrolledbackdelete(4);');

answered Jul 19, 2010 at 12:15

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A.N.M. Saiful Islam Over a year ago

Thanks a lot!!! It works!!! The mistake i did was calling two queries from a single mysql_queryfunction call.

Collectives™ on Stack Overflow

MySQL Transaction From PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related