Is This How To Send Email And Update Mysql At The Same Time?

[dev-ria]

hello,

i have a script im using to send an email and update certain fields in mysql. what i need is to prevent the email from being sent if the db did not update and prevent the db to update if the email was not sent. i use phpmailer

 

here is what i have so far...

 

http://semesterold.com/code.txt

 

id appreciate any help

[Psycho]

Well, let's think about this for a moment. You only want both processes to occur if both succeed. Well, that's not logically possible. You have to do one before the other. So, your only option is to do one first. If it fails then don't do the second. If the first process succeeds then the second one fails then you need to undo the first process.

 

If you send an email there is no way to undo that, so your only option is to start with the DB query. I believe in MS SQL there is a way to do a "tentative" query where it verifies that the query will execute and then you can do something else before executing it. But, assuming you are using MySQL, I don't know if that is possible. So you could do something like this:

 

1. Before doing the update query, do a select query on the record and store the current values

2. Perform the update query

3a. If the update query fails then stop

3b If the update query succeeds then attempt to send the email

4a If the email succeeds, stop

4b If the email fails, the run a new update query to set the value back to what they were previously using the data from step 1

 

But, really, this is probably all overkill. If your code is clean and logically built there is very little chance of either the email or query failing short of the DB or email servers going down. I'd just make a determination of which process is more important. I would think it would be worse for the email to be sent if the DB wasn't updated rather than the converse. So, I would do the DB update first.

[dev-ria]

you make a very good point. I didn't think there would be a way but I wanted to be sure and wanted a second opinion. I think that's a good idea. I appreciate your response! Very informative!

 

Thanks!

[kicken]

The most correct way to handle something like this is to use transactions. To do this with MySQL you need to be using InnoDB tables instead of MyISAM, and you should be using mysqli or PDO as your access layer. With PDO the process would go something like this:

$db = new PDO('mysql:host=localhost;dbname=yourdb', 'yourusername', 'yourpassword');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
$db->beginTransaction();

$stmt=$db->prepare("UPDATE domaindata SET email_sent_date = DATE_FORMAT(NOW(), '%m-%d-%Y'), email_sent_userid= :by, email_sent_id = :rndid WHERE combo = :showid");
$stmt->bindValue(':by', $_SESSION['userid']);
$stmt->bindValue(':rndid', $RandID);
$stmt->bindValue(':showid', $show_id);
$stmt->execute();

/* ... email setup stuff */
if (!$mail->Send()){
	throw new RuntimeException('Email failed');
}

$db->commit();
}
catch (Exception $e){
$db->rollback();
}

 

What that does is begins a database transaction and then does the update. If the update fails an exception will be thrown which causes it to skip the email code and rollback the transaction. If the email fails it will throw an exception and also cause the transaction to be rolled back. Rolling back the transaction will revert the db to how it was when the transaction started, basically undoing the UPDATE query.

 

If for some reason you cannot use transactions, I would probably order the code to try and send the email first, and update the DB if that succeeded. A failure sending the email seems more likely to me than a failure doing the update.