| skinny monkey 2004-08-18, 3:57 pm |
| i have three tables:
User [id, name, surname, email]
User_Login [id, login_id, password]
User_Privilege [id, Privilege_Level]
(id is pk)
As mysql doesn't support multi table inserts, when i add a user i
am having to call 3 functions, to add the data to each relevant
table.
What i am working on, is if there was an error, i am trying to
simulate a rollback.
My function looks like this:
PHP:
Function AddUser
{
status = InsertPersonalDetails();
if(status == false)
{
return false;
}
status = InsertLoginDetails();
if(status == false)
{
//need to delete information inserted into PersonalDetails (User)
table
Rollback();
return false;
}
status = InsertPrivilegeDetails();
if(status == false)
{
//now need to delete info from PersonalDetails (User) table and
LoginDetails (User_Login) table
Rollback();
return false;
}
}
The help i need is with the function Rollback - i am looking to
perform a multi-table delete, using a join, but i can't quite
figure it out.
What i am looking for is one sql statement that can look for any null
values in a completely joined record - if its null, then the insert
must've failed, therefore delete the record and its relevant
joined data.
eg, If the first two inserts are ok, then the third insert creates an
error, it will delete the user information that was entered in the
user details and the user login tables. An error generated in the
first insert is ok as it will break from the function.
I have written the following:
SELECT user.* from user LEFT JOIN user_privilege ON user.id =
user_privilege.id WHERE user_privilege.privilege_level IS NULL
i think this is ok (can someone confirm for me?), but i can't
translate it into a delete statement.
Thanks for the help!
----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums
|