# [web] Simultaneous operations inside query / Time occurring between two other times

This topic is 5461 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

With MySQL, 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. QUESTION 1a. Inside (or just after) the query that searches for available seats, I need to SIMULTANEOUSLY mark those seats as "on hold". 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.) 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. 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 seats to see if they were on hold, but were never taken, and make them available again. 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. What do people think of this, and what methods have you used in similar situations? 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. 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: SELECT stuff from table WHERE ($time > start_time) AND ($time < end_time) Thanks in advance! Simon.

##### Share on other sites
1a) It sounds to me as if you'll need support for TRIGGERS which essentially trigger other actions (such as queries to update tables) upon a certain event in the database. In your case, the trigger would update the status of the seats as the original query returns.

1b) This could be achieved by setting up an application-wide timer to update the table again if the seat isn't reserved in 2 minutes. Otherwise, you could set a "reserved time" field that is compared within the query to return only available seats (eg: query time compared to reserved time (if any) > 2 mins).

Now for some bad news. Triggers aren't available in current versions of MySQL and they're unlikely to surface before 5.1 or so. The T-SQL style support in MySQL is severely lacking and only really basic transactions are around. Stored procedures have only just surfaced in MySQL 5, which puts it years behind MSSQL server at best. You could emulate such support in PHP/ASP as functions by calling a function as soon as your query returns, but it's slower and not as elegant as triggers but you could give it a try.

2) For time releated functions, check out the relevant page on MySQL.com. I'm thinking TIMEDIFF would suit your needs here.

• ### Game Developer Survey

We are looking for qualified game developers to participate in a 10-minute online survey. Qualified participants will be offered a \$15 incentive for your time and insights. Click here to start!

• 15
• 22
• 17
• 13
• 14