Dynamically check timetable for clashes - jquery/php

Question

Hi

I'm making a timetable site for a music festival. The purpose of this site is for people to choose acts they want to see and compile their own personal timetable for the event. At the moment the site has 2 tables:

1. The full list of acts on at the festival
2. The user's personal timetable

Here is my problem. Everytime an act is added from the full list to the user's personal timetable, i want a function which will iterate through every table row of the user's timetable and highlight a clash. This clash would then have a different format (for arguments sake, lets say it will turn the background of the row red, to indicate a clash).

How would i do this?

Table code:

<table id="fri_myTimes">
    <thead>
    <tr><th>Act</th>
    <th>Start Time</th>
    <th>End Time</th>
    <th>Duration</th>
    </tr></thead>
    <tbody>
<tr id="band-Young-Guns class="Main">
<td>Young Guns</td>
<td>12:00:00</td>
<td>12:30:00</td>
<td>30</td>
<td><div id="btn-Young-Guns" class="del">&nbsp;</div></td>
</tr>
<tr id="band-Frankie-And-The-Heartstrings" class="NME-/-Radio-1">
<td>Frankie And The Heartstrings</td>
<td>12:00:00</td>
<td>12:30:00</td>
<td>30</td>
<td>
<div id="btn-Frankie-And-The-Heartstrings" class="del">&nbsp;</div></td></tr>
</tbody>

Answer 1

I would use PHP, insert the new time in the table and then lay out the user timetable for the user. When you are laying out the timetable, select all the user's selected acts and display them in order of the times

user
id | username | email | etc
33 | BillyBob | ...   |
... etc ...

timetable
id           | user_id | act_id
1            | 33      | 5
2            | 33      | 6
3            | 33      | 19

acts

id | start_time        | end_time         | title             | description
5  | 2010-11-20 11:00  | 2010-11-20 13:00 | Smashing Tomatoes |
6  | 2010-11-20 12:00  | 2010-11-20 14:00 | Stray Dogs        |
7  | 2010-11-21 11:00  | 2010-11-21 13:00 | Mister Googo      |
....
19 | 2010-11-21 12:00  | 2010-11-21 14:00 | Reeses Pieces     |

select t.*, a.title, UNIX_TIMESTAMP(a.start_time) as epoch_start, UNIX_TIMESTAMP(a.end_time) as epoch_end from timetable t join acts a on ( t.act_id = a.id ) where t.user_id = 33;

$timetable = array ();
while ( $rs = mysql_fetch_array ( $r, MYSQL_ASSOC ) )
{
    foreach ( $timetable as $tt )
    {
        if ( $rs['epoch_start'] > $tt['epoch_start'] and $rs['epoch_start'] < $rs['epoch_end'] )
        {
            $rs['conflict'] = $tt['id'];
        }
    }

    $timetable[$rs['id']] = $rs;
}


foreach ( $timetable as $toss => $tt )
{
   echo $tt['title'];
   if ( $tt['conflict'] )
   {
       $conflict = $timetable[$tt['conflict']];
       echo "note: this time conflicts with {$conflict['title']}";
   }
}

...Untested code but that's the gist. I guarantee there are some syntax and logic errors in there but that's a concept and one approach.

I went away from the time_id thing; it didn't really help that much.