MySql transqactions with dependent queries
I really hope you can help!
I use the following function to send a message in my PHP/MySql application:
public function sendMail($sender_id, $recipient_id, $subject, $message) {
$q = "INSERT INTO MAIL_MESSAGE (subject, message, date) VALUES ('$subject', '$message', NOW() )";
$s = mysql_query($q);
if (mysql_affected_rows()==1) {
$message_id = mysql_insert_id();
$q = "INSERT INTO MAIL (user_id, sender, message_id) VALUES ('$recipient_id','$sender_id','$message_id')";
$s = mysql_query($q);
if (mysql_affected_rows()==1)
return true;
}
return false;
}
I use two tables (MAIL_MESSAG开发者_开发百科E and MAIL) becuse the same '$sender_id' can send the same message to multiple '$recipient_id'.
Now the problem is that if the last query fails I have a row in MAIL_MESSAGE without the correspondent row in MAIL. How can I solve this?
Transactions can help, but I don't know how to make it work!
Thanks in advance for your help.
Add "begin" at the start of the sequence to start a transaction. Commit the transaction only if both inserts succeed, otherwise rollback the transaction.
I do not know PHP, but based on your sample it would look something like the following:
public function sendMail($sender_id, $recipient_id, $subject, $message) {
$s = mysql_query("begin");
$q = "INSERT INTO MAIL_MESSAGE (subject, message, date) VALUES ('$subject', '$message', NOW() )";
$s = mysql_query($q);
if (mysql_affected_rows()==1) {
$message_id = mysql_insert_id();
$q = "INSERT INTO MAIL (user_id, sender, message_id) VALUES ('$recipient_id','$sender_id','$message_id')";
$s = mysql_query($q);
if (mysql_affected_rows()==1) {
$s = mysql_query("commit");
return true;
}
$s = mysql_query( "rollback" );
return false;
}
精彩评论