I'm working on a site where people can sign up for "time slots" for classes. The available date/time slots are stored in a database. When the page opens, the PHP script checks for all entries where the "booked" variable is set to "no" (the default) and displays them as available slots. Only one person should be able to sign up for each time slot.
I have the IPN script which will update the database upon payment completion and change the time slot "booked" variable to "yes". All good.
In order to avoid possible double-bookings, I need to temporarily remove a time slot once someone clicks on it. But then, what happens if someone signs up but then never actually pays? (That is, they fill out their name, email, etc, and it goes into the database but then they never click over to complete PayPal payment?) My IPN script resets the DB table variables if the payment is cancelled or not verified, but there's nothing to reset it if they never GO to PayPal in the first place.
Is there some way to automatically reset the "booked" variable back to "no" after a time period - say, 30 minutes - if PayPal hasn't updated it? Or any other suggestions how to handle this?
As the comments are stating you should add a timestamp and status field in the table structure if you don't have them already. The script running the Paypal API should update the status to pending when someone goes to "book" the session. Now in order to clear those that are not paid after your mentioned "30 minute" time, within the same script that is displaying time slots to those interested, like you describe here:
"When the page opens, the PHP script checks for all entries where the "booked" variable is set to "no" (the default) and displays them as available slots."
A small function should be added in the beginning of this script prior to them being displayed to the user that deletes those currently in the "pending" status for more then 30 minutes. Something like this:
$deletetime = strtotime('-30 minutes');
mysql_query("DELETE FROM time_slots WHERE timestamp < '$deletetime' and status = 'pending'");