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
Begin
SET FOREIGN_KEY_CHECKS = 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_CHECKS = 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_CHECKS = 0.
Any ideas why this may be happening? Please let me know.
Thank you in advance,
Abhi

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.
Abhi

Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.