Update / Check Values Problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
WizyWyg
Forum Commoner
Posts: 92
Joined: Tue Aug 06, 2002 7:20 pm

Update / Check Values Problem

Post by 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:


Code: Select all

//-------------------------------
// 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
WizyWyg
Forum Commoner
Posts: 92
Joined: Tue Aug 06, 2002 7:20 pm

Post by WizyWyg »

??
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

well something like this will give you your overlaps, essentially you need to compare the start of event 1 against the end of event 2 and vice versa:

Code: Select all

select ?
  from events as e1, events as e2
 where e1.start < e2.end
   and e1.end > e2.start
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Well if you're updating an existing event could you add a "AND event_ID<>$update_ID" style condition to your existing where clause to skip over checks against the current event? Obviously you'll need to change event_ID and $update_ID to the names of these types of things in your database/code.
WizyWyg
Forum Commoner
Posts: 92
Joined: Tue Aug 06, 2002 7:20 pm

Post by WizyWyg »

so something like this?

Code: Select all

$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) AND event_id<>$fldevent_id";
Post Reply