Sorry that should have been max(tourney.finish) :-[
graeme
graeme wrote:
Set your select to group by userid, select on min(tourney.start) value and max(tourney.start) value where closed = 1 etc.
graeme.
jake@xxxxxxxxxx wrote:
I have an interesting problem I'm trying to solve. Right now, I'm keeping
track of sessions starting and ending times with a PHP datetime field, and calculating the total time
spent in all sessions with the new PHP time command... something like this:
$total_minutes = mysql_query("select ((sum(time_to_sec(timediff(tourney.finish, tourney.start))))/ (60*60)) FROM tourney WHERE tourney.closed = '1' AND tourney.userid = '$userid'");
but the problem is that in some cases, there are overlapping "sessions" or
tourneys. That is, someone might have a tourney.start at 10:00 with a tourney.finish at 10:45
and start another tourney.start at 10:15 and finish at 10:55. right now, i'd get the sum of
both sessions, 45 minutes + 40 minutes = 85 minutes, where I'd rather have the calculation of 10:00
(the earliest start) to 10:
55 (the latest finish), for a total of 55 minutes.
I can't get my head around the right logic to use to get to an answer like
that. There may be hundreds of registered tourneys, so testing one against the rest to see if
there is a matching time seems very wasteful and sloppy.
Any suggestions on how I might proceed? Perhaps a one time query when I
add the record into the database (that would make changing the record difficult) to compare the
session time to other session times somehow?
Thanks in advance
Jake
-------------------------------------------------------------------- mail2web - Check your email from the web at http://mail2web.com/ .
-- Experience is a good teacher, but she sends in terrific bills.
Minna Antrim
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
