Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Transactions in MySQL using PHP
May 17, 2005, 12:27 am
rate this thread
I am a general user of PHP and Mysql, not an expert. But I am having
we have two tables called
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
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
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,
Re: Transactions in MySQL using PHP
I am sorry to post this but I found the bug in my code. The above logic
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.
- » Split by spaces but not when phrase is inside quotes...
- — Previous thread in » PHP Scripting Forum