Transactional Prepared Statements

[Fmdpa]

I'm modifying my user login system so that the user doesn't accidentally get created if the registration email fails to send.

//begin transaction//insert the user into the database$mail = @mail(...);if (!$mail) {   //rollback} else {   //commit}

That's all great, but is there a way I can do this using mysqli-stmt or pdo placeholders to interpolate the user's info into the INSERT query, and still be able to use transactions?

[Synook]

Why don't you just try to send the email before inserting the details into the database? That way you don't have to worry about transactions at all.

[Fmdpa]

I'm using transactions because both actions depend upon the success of the other. I think switching them around like you suggested would be worse because they might receive the email without the account actually getting created (if there were a db error). So by suggesting a different option, are you implying that there is no way to do a transactional prepared statement?

[boen_robot]

Transactions occur as a sequence of separate queries, and PHP allows you to start and end transactions with separate methods, so I don't see why you should not be able to have one query with the insert, and then rollback in case of mail() failure.Why the need for using prepared statements anyway? Have you tried to first start a transaction and then initialize the prepared statement(s)?

[Fmdpa]

I wanted to figure out how to do it with prepared statements, just 'cause. I went back to see if I could make it work in plain mysqli, and surprisingly I couldn't!

$mysqli->autocommit(false);$mysqli->query("INSERT...");$mysqli->rollback();

The rollback didn't work. It inserted successfully (which is not what I wanted...). What's wrong?

[birbal]

you have to set autocommit off

SET AUTOCOMMIT=0

by default its commit after query automaticaly.

[Fmdpa]

That's what mysqli's autocommit() method is for, I believe. That's what the examples in the PHP manual look like anyway.

[birbal]

ohh..yes it do the same..soryy i missed that statement..

$mysqli->autocommit(false);$mysqli->query("INSERT...");$mysqli->rollback();

did you start a transaction?...you should start a transaction first.(i cant see any statement for start a transaction here)...

[Fmdpa]

Mysqli handles transactions differently than plain SQL. check this page: http://us2.php.net/manual/en/mysqli.rollback.php

[7php]

- Why don't you use PDO instead? It involves prepared statement + :named_placeHolders + transactions. Have a look hereyou do something like:$conn_obj->BeginTransactions()try{//processing = insertionsif (mail fail) //throw new PDOException('email fail');if (OK) //commitTransaction();}catch(PDOException $err){if(FAIL)//rollBack()}///NOTE: this is just to give an idea, the syntax might be wrong, just confirm it via the php.net link I mentioned above- A suggestion: why don't you use activation email instead? Like when user register, you do your normal insertions + any email. But when you insert, you insert with 'user_activation=0' or soemthingThen when the user receives email with a 'confirmation link', he clicks and then updates 'user_activation=1'Behind the scene, once in a while you could run a cron to delete all users where 'user_activation=0' and signup_date < (date_of_your_choice)All the best!

[Fmdpa]

Thanks for the ideas, 7php.