Rip Curl
|
| Posted: 05/22/2003, 12:38 PM |
|
Working with CC php and templates
Outside of CC, using PHP, I had to work out a code to figure out how to make a form, upon submission, check to see if a record exists, meeting three criteria, and if it does, return a message saying so; and if it doesn't, go ahead an insert the new record.
Basically, its an events scheduler, that someone can "reserve" a room for a meeting or such, for a certain period of time. I just modified the existing Events Scheduler example from CC.
However, since these are for rooms, I want to make sure that if someone tries to book a meeting, to make the form check for start time, end time, and the room id before submission to see if it is taken during that time. If not, then go ahead and insert/add the new event.
I have a custom SELECT code:
$sql = "SELECT COUNT(*) FROM event WHERE ($fldevent_start BETWEEN event_start AND event_end) OR ($fldevent_end BETWEEN event_start AND event_end) AND roomid=$fldroomid;";
So this checks to see if events in the DB match to the submitted event_start time, event_end time, and roomid.
$result = $db->query($sql);
The resource handler for query.
And I dont know for sure if this custom IF ... ELSE statement will work with CC (involving num_rows )
if(mysql_num_rows($result) == 0)
{
mysql_query("INSERT INTO event (event_start, event_end, room_id) VALUES ($event_start, $event_end, $room_id);
}
else { $error_message = "There is already an event at $event_start"; }
Any ideas on how to implement this custom code into CC for the form submission page?
|
|
|
 |
RipCurl
|
| Posted: 05/22/2003, 6:48 PM |
|
Okay tried the following:
//-------------------------------
// Create SQL statement
//-------------------------------
switch(strtolower($sAction))
{
case "insert":
//-------------------------------
// event Insert Event begin
$formated_datetime = "$WNDate $event_start";
$fldevent_start = strtotime($formated_datetime);
$formated_datetime2 = "$WNDate $event_end";
$fldevent_end = strtotime($formated_datetime2);
// custom sql
$sql = "SELECT COUNT(*) FROM event WHERE ($fldevent_start BETWEEN event_start AND event_end) OR ($fldevent_end BETWEEN event_start AND event_end) AND roomid=$fldroomid;";
$result = $db->query($sql);
if(mysql_num_rows($result) == 0)
{
$sSQL = "insert into event (" .
"event_title," .
"event_desc," .
"event_start," .
"event_end," .
"event_date," .
"roomid," .
"team," .
"userid," .
"type)" .
" values (" .
tosql($fldevent_title, "Text") . "," .
tosql($fldevent_desc, "Memo") . "," .
tosql($fldevent_start, "Text") . "," .
tosql($fldevent_end, "Text") . "," .
tosql($fldWNDate, "Date") . "," .
tosql($fldroomid, "Number") . "," .
tosql($fldteam, "Number") . "," .
tosql($flduserid, "Number") . "," .
tosql($fldtype, "Text") .
")";
}
else
{
$seventErr .= "<b><font color=\"#FF0000\">There is already an event already scheduled. Please pick a different time and/or room</font></b>";
}
// end custom sql
// event Insert Event end
//-------------------------------
break;
case "update":
//-------------------------------
// event Update Event begin
$formated_datetime = "$fldWNDATE $fldevent_start";
$fldevent_start = strtotime($formated_datetime);
//
$formated_datetime2 = "$fldWNDATE $fldevent_end";
$fldevent_end = strtotime($formated_datetime2);
// event Update Event end
//-------------------------------
if($sSQL == "")
{
$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;
}
//-------------------------------
//-------------------------------
// Execute SQL statement
//-------------------------------
if(strlen($seventErr)) return;
if($bExecSQL)
$db->query($sSQL);
switch($sAction)
{
case "insert":
//-------------------------------
// event AfterInsert Event begin
$last = mysql_insert_id ();
//append the value to the sParams variable. This should be done only if the record form has transfer input parameters
$sParams .= $last;
// event AfterInsert Event end
//-------------------------------
break;
}
header("Location: " . $sActionFileName . $sParams);
exit;
//-------------------------------
// event CustomAction Event end
//-------------------------------
But it returns the "error" message that its already taken, when it is defnitely free.
|
|
|
|
|
