MySql transqactions with dependent queries

Question

Hi everybody, 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_MESSAGE 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.

Answer 1

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;
    }