Is it possible to hold MySQL commit over dialogs?

[jayteepics]

Hit Checkout button the following happens..

 

  $conn = db_connect();

  // we want to insert the order as a transaction
  // start one by turning off autocommit
  $conn->autocommit(FALSE);

 

Then order, order_items and customers tables are updated

 

If I then include this code after the order is on the database it commits fine

 

// end transaction
  $conn->commit();
  $conn->autocommit(TRUE);

 

However I think i have a huge sequence error in that it is only at this point I ask them for the payment card details!

 

If the payment fails I've still commited.

 

I tried moving the $conn->commit(); to the end of the card processing but it just stops at that command I guess because when I went back to solicit the card payment details the plot got lost.

 

I hope this is making sense...

 

Is it possible to keep the transaction / unit of work pending i.e not committed until the result of the next dialog and only commit on a successful card payment else backout?

 

The problem is I can't go to the card payment until I know the entire order process worked.

 

Jamie

[WebStyles]

as a quick fix, you could store the orders in a temporary database, and upon successful payment, move them to a definitive one.

 

 

[Psycho]

EDIT:WebStyles solution is similar, but I would use the current table and just add a status column:

 

Create the order with a temporary status (e.g. pending payment verification), then proceed to the payment process. If payment succeeds update the order status to paid. If not, delete the order.

[DavidAM]

When a script finishes, all database connections are closed. When a database connection is closed, any un-committed transactions are rolled-back. So you cannot carry a transaction from one page to the next. Even if you could, with persistent connections (which I doubt), you do NOT want to do it. Consider this:

 

A guy gets to the payment page and his wife screams, he runs to the kitchen to put out a grease fire, calms his wife down, cleans up the kitchen, calls the alarm company to tell them the house is NOT on fire, opens the windows, drags in the fans, airs out the room, calms his wife down, calls domino's (cause dinner is ruined), ...

 

All this time your script is holding a transaction open. During a transaction, the user has locks on the table. These locks can prevent other users from updating the database. So this one guy has effectively shutdown your store.

 

I would suggest completing the order in the database with a flag that indicates NOT PAID. Then have the payment process update this flag as part of its processing. You can setup a cron job to delete unpaid-old orders every night. Of course, your other processes may have to take this flag into account when considering inventory available, etc.

 

[jayteepics]

Many thanks all of you - great feedback and what I expected just never worked with browsers before other than static web pages.

 

David thanks for taking time out to hammer the point home with humor ;-)

 

I already have a status flag and will now proceed with flag set to PAID if the card payment works and delete everything for that order if it fails.

 

Jamie