Automatically changing a value after it is updated in MySQL
Hi there,
This may sound weird but here's my situation:
I'm currently coding an online booking system for a local theatre company through which people can view seat availability, select which seats to buy and then pay online for the seats via PayPal.
However, the paying process must be separate to the process of adding the order to a MySQL database and changing the seats to booked.
Therefore, I would like to make it so that when they order seats they are then given 30 minutes to complete their paypal checkout, otherwise the seats will be changed back to vacant.
So is there a way to store almost a temporary value within a MySQL database which will only last for a certain amount of time?
Hope I didn't make that too difficult to understand...
Thanks.
JCoster
Greg K posted this at 18:12 — 13th November 2007.
He has: 2,145 posts
Joined: Nov 2003
Just off of the top of my head this is what I would do:
Add a field to the table called "HoldTo", and make it hold a time stamp value
Now if a client thinks they want those seats, set the current time stamp value in that field
Every time you do a search for available seats, add to the WHERE clause a statement that also only grabs seats whose HoldTo time stamp is at least 30 minutes old.
When they go to place the order (or each page they visit while looking around), look up the time difference and let them know "Seats will be hold for xx more minutes" or something. On each step of the payment of the order, always check to make sure and right before actually processing the payment, extend it for five minutes so in cause they have to reenter card info, etc.
Also allow them to have a link to cancel the hold on the seats. If they don't clear it and don't order, after 30 minutes they will become part of the search results again.
Just make sure at each step you check for availability. I may go to the site, and get told seat 44 is available, but I try to call my wife to make sure she likes that seat. 2 minutes later you log on, and you choose seat 44. Because I'm just looking at a page of what is available when I first loaded it 5 minutes ago, I can still click on a link to choose seat 44. So it needs to recheck when i select it cause you picked it while i was on hold....
-Greg
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.