Transactions in MySQL using PHP

[Abhi]

I am a general user of PHP and Mysql, not an expert. But I am having
following problem:
we have two tables called
Users(uID, uName)
Users_Groups (gID, uID, isAdmin)
uID is Autoincreament in Users and Foreign key in UserGroups table.
I am using transactions to insert a new user into User Table and
immediately insert into Users_Groups during the same transaction. It is
an atomic operation. This happens in the same PHP script. I tried
using use mysqli and old mysql_ calls. This transcation works well when
I execute in an IDE like mysqlControl Center. But in the PHP script.
It does not go through Here is the PHP Pseudocde
Begin
SET FOREIGN_KEY_CHE CKS = 0
Insert into Users Table
If (sucessful) Then
Retrieve userid = mysql_insert_id

Insert into Users_Groups values(gID, userid, 0)
if (sucessful) Then
COMMIT
else
ROLLBACK
SET FOREIGN_KEY_CHE CKS = 1
In this case I am able to retrieve and print the inserted
mysql_insert_id for Users table but when I try to insert it into the
Users_Groups table it fails and ROLLS-BACK. My guess is it fails the
Foreign key constraint even though I SET FOREIGN_KEY_CHE CKS = 0.
Any ideas why this may be happening? Please let me know.
Thank you in advance,
Abhi

[jabhijit@gmail.com]

Re: Transactions in MySQL using PHP

I am sorry to post this but I found the bug in my code. The above logic
works well.
It was basically the problem of using mysql_insert_id for a table that
does not have an autoincreament column in order to find out if
insertions worked or not. Instead using mysql_affected_ rows() fixed
the problem. Fresh mind in the morning
helped looking at the problem.
Abhi