By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,409 Members | 1,524 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,409 IT Pros & Developers. It's quick & easy.

Transactions in MySQL using PHP

P: n/a
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,

Jul 17 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
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.

Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.