CODEWITHSUNDEEP
phpmysqltransactions
Kristian
Kristian

Reputation: 21810

How to determine success of MySQL transaction

In a regular single query statement to MySQL, I can use mysql_affected_rows()==1 to determine whether or not something was updated. Now suppose I am trying to perform a transaction of the following:

USE myDB;
START TRANSACTION;
UPDATE members SET member_active=0 WHERE member_id = 53;
UPDATE member_subscriptions SET subscription_active=0 WHERE member_id = 53;
COMMIT;
ROLLBACK;

Since I have those two update statements, should I could on a successful transaction being equivalent to mysql_affected_rows()==2 ?

OR, is there a better way I should be checking for success?

Upvotes: 4

Views: 2161

Answers (3)

Omesh
Omesh

Reputation: 29081

Yes checking mysql_affected_rows()==2 is an alternative way from application level.

Its going to be 100% successful(commit) or nothing (rolled back) as you are using proper transactions.

EDIT: You can use ROW_COUNT() function in MySQL to get affected rows from last query as:

USE myDB;
START TRANSACTION;
UPDATE members SET member_active=0 WHERE member_id = 53;
SELECT ROW_COUNT() INTO @count1;
UPDATE member_subscriptions SET subscription_active=0 WHERE member_id = 53;
SELECT ROW_COUNT() INTO @count2;
COMMIT;
ROLLBACK;

then the sum of these should be equals to 2 

SELECT ((@count1 + @count2) = 2) AS status;

Upvotes: 1

Suleman Ahmad
Suleman Ahmad

Reputation: 2103

If you have track of both transactions and can have even better judgment which transaction is not performed and what would be the reaction.

USE myDB;
START TRANSACTION;
UPDATE members SET member_active=0 WHERE member_id = 53;
$count1= mysql_affected_rows();
UPDATE member_subscriptions SET subscription_active=0 WHERE member_id = 53;
$count2= mysql_affected_rows();
COMMIT;
ROLLBACK;

$total_affected_rows = $count1 + $count2;

Upvotes: 1

Arun Jain
Arun Jain

Reputation: 5464

You should hold the value of mysql_affected_rows in variables. And total updation will be some of those variables.

Upvotes: 1

Related Questions