Update / check Value problem

[WizyWyg]

While i go on figuring out Flat Files, I've run into a problem with one of the DB sites that i have

I have an event scheduler that basically that someone can fill out to add events to a particular room we have. It checks to see if a time or room are taken , and returns an error message if it does. If isn't taken, then it adds the event.

I implemented this on an UPDATE as well, and now running into a problem. If someone tries to update an event's time, the error message will happen if its for a time within the original selected time of hte event.

For example

Event A
Start Time 9:00 am
End Time 11:00 am
Room 1

If someone tried to update the information so that it started at 9:30 instead, it returns the error message that the room is already taken for that time.

This is the following code I use in my update sequence:

//-------------------------------
// event Update Event begin
$formated_datetime = "$fldWNDate $start";
$fldevent_start = strtotime("$formated_datetime");
$formated_datetime2 = "$fldWNDate $end";
$fldevent_end = strtotime($formated_datetime2);
list( $yr, $mo, $dy ) = split( "-", $fldWNDate );
list( $hr, $mn, $se ) = split(":", $start);
list( $hre, $mne, $see ) = split(":", $end);
$new_start = mktime($hr,$mn,$se+2,$mo,$dy,$yr);
$new_end = mktime($hre,$mne,$see-2,$mo,$dy,$yr);
$sql = "SELECT 1 FROM event WHERE ($new_start BETWEEN event_start AND event_end AND roomid=$fldroomid) OR ($new_end BETWEEN event_start AND event_end AND roomid=$fldroomid)"; 
$result = $db->query($sql);
// event Update Event end
//-------------------------------
      if(mysql_num_rows($result)) 
            { 
            $seventErr .= "<b><font color=\"#FF0000\">The room is already in use for this time. Please pick a different time and/or room</font></b>"; 
            } 
        else 
            { 
        $sSQL = "update event set " .
          "event_title=" . tosql($fldevent_title, "Text") .
          ",event_desc=" . tosql($fldevent_desc, "Memo") .
          ",event_start=" . tosql($fldevent_start, "Text") .
          ",event_end=" . tosql($fldevent_end, "Text") .
          ",event_date=" . tosql($fldWNDate, "Date") .
          ",roomid=" . tosql($fldroomid, "Number") .
          ",team=" . tosql($fldteam, "Number") .
          ",userid=" . tosql($flduserid, "Number") .
          ",type=" . tosql($fldtype, "Text");
        $sSQL .= " where " . $sWhere;
      }    break;
  }
//-------------------------------

How can I get it so that if its updating that any time chosen in between the original is still acceptable, BUT, still check to make sure that any other time is not in conflict

For example

Event A
Start 9:00 am
End 11:00 am
Room 1

Event B
Start 12:00 pm
End 2:00 pm

If on change to Event A to 11:00 am start and 12:30 end would return an error, but if change to Event is is 9:30 am to 12:00 pm, it wouldnt

Thanks in advance

[tsinka]

Hi,

I hope I got it, but you already have a query that checks if the start and end time is valid. If you want to update an event you would have to change the query so that it would exclude the given event by e.g. AND event_id<>$cur_event_id so it would only look for conflicts with other events.

[WizyWyg]

Thanks that make sense, though I have no idea on how to implement that

[tsinka]

Hi,

I think it would be sufficient to modify the SQL query:

SELECT 1 FROM event WHERE ($new_start BETWEEN event_start AND event_end AND roomid=$fldroomid) OR ($new_end BETWEEN event_start AND event_end AND roomid=$fldroomid) AND eventid<>$eventid"; 

If you want to check if a new event you want to store has a conflict with an existing event you can use the query without the exclusion of the eventid.

If you want to update an existing event you need to get the id of that event and use the query with the exclusion of that event id to make sure that you don't accidently check if the event conflicts with itself. I think you already have the event id in $sWhere.

[WizyWyg]

Im getting an Invalid sql query on that, so I'll play with it for a bit to see what is happening.

[WizyWyg]

Yup, fixed it:

$sql = "SELECT 1 FROM event WHERE ($new_start BETWEEN event_start AND event_end AND roomid=$fldroomid AND eventid<>$pPKeventid) OR ($new_end BETWEEN event_start AND event_end AND roomid=$fldroomid AND eventid<>$pPKeventid)";

Just included it within each "between" statement and that worked it out.