Jefferson, > I'm writing a web application in PHP to do referee scheduling for soccer > games. For each game (a single row in a table) I have a cell for > referee. The cell stores an integer that I can do a join on with another > table of people. I need a way to prevent people from being able to > schedule themselves for multiple slots at the same time. (All I care > about is start time, for now I'm going to ignore the issue of a game > ending after the next one has started.) I have tried doing just a unique > index on referee, date, and time but the issue is I use a 0 to indicate > that the slot is open. Because of that I don't see a way to do it > natively in mysql as such each time I do an insert I think I am going to > need to do a select right before to make sure there no conflicts. Is > there any better more efficient way to do this? Perhaps natively in > mysql. TIA
Speaking for referees everywhere, I'd say not to bother with 'time' because I wouldn't want to cope with more than one 90-minute game in a day - but perhaps you're working on rapid-fire short-game tournaments or somesuch... There are two issues here: firstly has a referee been assigned to control each/every game - or does this (one) game have a referee assigned to it? Secondly, when a referee is assigned, is (s)he in fact 'available'. Sounds like we should be using a project planning package! The first question is answered by SELECT gameId FROM games WHERE refereeId = 0; and/or SELECT refereeId FROM games WHERE gameId = ?; The second 'fails' if you can SELECT gameId FROM games WHERE refereeId = ? AND gameDate = ? AND gameTime = ? (ie no rows returned implies the referee is available, one row that (s)he is assigned, and more than one row that you have a scheduling snafu!) The two queries are logically quite separate. Will attempting to implement both aspects in a single query actually help your system? MySQL: Insofar as your realisation that games occupy time slots 'from' and 'to' moments in time, you might like to check out the BETWEEN comparator. Many consider it good practise to 'check' with a SELECT before performing an UPDATE or INSERT. Soccer refereeing: Please consider that hard-working referees deserve a rest between matches! Also that running another game is not the only reason why a referee might not be available to you. Hope this helps, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
