I'm looking to do something similar to a feature found on Ticketmaster.com, where you select your seats at a venue, and then you have two minutes in which to take or leave them.<br /><br />QUESTION 1a. Inside (or just after) the same query that searches for available seats, I need to SIMULTANEOUSLY mark those seats as "on hold".<br /><br />I've only read about, but not yet used MySQL transactions, and wonder if this simultaneous "search-and-hold" would/could be done in a transaction (of multiple queries) or if possible inside just one query? Either way, how would I do this "search-and-change" of data? (i.e. changing fields in the rows that are returned by the query.)<br /><br />QUESTION 1b. So the seats have been put on hold. But by putting seats on hold, the user may leave them - by either going over their two minutes, or just navigating away, closing their browser, or even the browser/computer crashing etc. So I can't set a seat on hold as a binary yes/no flag, which may never get cleared if the user goes away abruptly.<br /><br />I also can't lock a table for two minutes, which is way too long, and I can't have an automated script scanning all the time through all tickets to see if they were on hold, but were never taken, and make them available again.<br /><br />So I was thinking, place the seats on hold by attaching to them a time two minutes into the future. During the next two minutes, these "on hold" seats are passed over by other user's searches because the current time is still before the "hold-until-time" - but if they never get purchased, then they become available again after two minutes.<br /><br />What do people think of this, and what methods have you used in similar situations?<br /><br />And so, if the user likes (and then pays for) this set of seats, I can just go through all the seats and mark them as permanently taken. <br /><br />QUESTION 2. While I'm here asking time-related questions, how can I determine in a MySQL query if a certain time falls between two others, because of the dilemma arising due to the circular nature of times i.e. any time can occur both inside AND outside of two times. I want rows to be selected, say when a certain event happens between 9:00 PM and 6:00 AM, and not sure if something like the following would work in all cases:<br /><br />SELECT stuff from table WHERE ($time > start_time) AND ($time < end_time)<br /><br />Thanks in advance!<br /><br />Simon.<!--content-->
Simultaneous Queries / Time Between Two Others
- Thread starter liunx
- Start date