Transactions in MySQL using PHP

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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
Insert into Users Table
If (sucessful) Then
    Retrieve userid = mysql_insert_id

Insert into Users_Groups values(gID, userid, 0)
if (sucessful) Then
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_CHECKS = 0.
Any ideas why this may be happening? Please let me know.
Thank you in advance,

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.

Site Timeline