Inserting into tables at once

[cougarreddy]

Hi..I am developing a website, where, database has three tables. "usertable","usercourse table" and other is "coursetable".

 

user table columns -> userid,name,password,status(dropped or not)

usercourse table columns -> userid,courseid,instructor,grade

coursetable->courseid,instructor

 

requirement is, when user acct is created by admin, user details or inserted into usertable, and also usercourse table. for example if there are three courses available in "course table", 3 records are created in "usercourse table".. that means, combination of "userid"+"courseid" is unique in "usercourse table"

 

My question is how can I make sure that when a user account is created records are updated into both tables or not into any. for now I am using code similar to this(using PHP)

<?PHP
$qry="INSERT INTO users values('**','**'.....)";
$usr_result=mysql_query($qry) or die("");
if($usr)
{
$qry="INSERT INTO usercourse values('**','**'.....)";
$courseusr_result=mysql_query($qry) or die("");
if($courseusr_result)
//success;
}
?>

for now, this makes sure that no records are inserted into usercourse table, if first query is unsuccessful. But what I want is, I want to make sure, no records are inserted into "users" if second query fails. I want to know how can I guarantee this.

 

Sry, I am a newbie not just to mysql, but to entire db stuff..

 

Any help is appreciated.

[gevans]

You need to have a look at transactions...

 

You may need to specify your table type, but I have it working on MySQL 5

 

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

[cougarreddy]

thanks