Page 1 of 1

Need help with a query

Posted: Tue May 20, 2003 4:18 pm
by WizyWyg
I have a form that a person fills out to reserve a room for an event.

I have the event_start and event_end in the tabl event to designate start and end times ( both are in UNIX Time stamp)

Rooms are given an id #.

When a person fills out the form, I want the form to check the event table to see if the start time for the new event is in conflict with existing scheduled events for that particular room they want and return an error message indicating such. IF there is no event, then just insert the new event.

Right now Im just testing out some PHP code to see how it could work

I know of :
SELECT 1 FROM table_name WHERE time BETWEEN start AND end
for MySQL query, but I dont know if I've constructed the query correctly.

table_name = event
time (?) = dont know which this refers to
start (?) = is this the time that is given by the form for the new event
end (?) = same as start

So I just constructed below:

Code: Select all

$db_name = "reserve";
$db_pw = "root";
$db_user = "root";
$db_server ="localhost";

$connection = mysql_connect($db_server,$db_user,$db_pw) or die("Couldn't connect to db.");
$db = mysql_select_db($db_name, $connection) or die("Couldn't select database.");

$sql = "SELECT 1 FROM event WHERE 1053459000 BETWEEN 1053457200 AND 1053460800 AND roomid=2";
$result = mysql_query($sql,$connection);

echo "$result";

1053457200 = 5/20/2003 9:00 am
1053460800 = 5/20/2003 10:00 am
1053459000 = 5/20/2003 9:30 am

Which is basically what i have.
I have an event scheulded for that time already, and the query is suppose to check to see if the new event that starts at 1053459000 return a row.

Posted: Tue May 20, 2003 5:41 pm
by liljester
may i suggest:

Code: Select all

<?php
#$event_start, $event_end, $room_id are variables from the form

$sql = "SELECT COUNT(*) FROM event WHERE $event_start BETWEEN event_start AND event_end AND room_id=$room_id;";

$result = mysql_query($sql,$connection); 

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"; }
?>
$event_start, $event_end, $room_id are variables from the submitted form. if the $event_start is between the event_start and event_end in the database, it will generate an error

Posted: Tue May 20, 2003 8:30 pm
by WizyWyg
According to MySQL 's manual, you cannot use COUNT() with WHERE clauses.

http://www.mysql.com/doc/en/Group_by_functions.html
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether they contain NULL values. COUNT(*) is optimised to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.
it can be used with the GROUP BY clause.

Posted: Wed May 21, 2003 12:13 pm
by liljester
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether they contain NULL values. COUNT(*) is optimised to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.
read this again. "COUNT(*) is optimised to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause." that says that it is very fast if there is no where clause. it *WILL* work if there is a where clause, it just wont be as fast =)

Posted: Wed May 21, 2003 4:24 pm
by WizyWyg
liljester wrote: read this again. "COUNT(*) is optimised to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause." that says that it is very fast if there is no where clause. it *WILL* work if there is a where clause, it just wont be as fast =)

I re-read it, and tried it out, and it was giving me a "unrecognized MySQL resource" as an error.
Re-wrote the code, and now it doesn't display it, but I got it work right without the Count() anyway:

Code: Select all

$db_name = "reserve";
$db_pw = "";
$db_user = "root";
$db_server ="localhost";
$db_table = "event";

$connection = mysql_connect($db_server,$db_user,$db_pw) or die("Couldn't connect to db.");
$db = mysql_select_db($db_name, $connection) or die("Couldn't select database.");

$fldevent_start = "1053567000"; 
$fldevent_end = "1053570600";
//SELECT 1 FROM table WHERE time BETWEEN start AND end
$sql = "SELECT * FROM event WHERE $fldevent_start BETWEEN event_start AND event_end AND roomid=1";
$result = mysql_query($sql,$connection);

if(mysql_num_rows($result))
{ echo "<b>time is booked</b><BR><BR><hr>"; }
else
{ echo "<b>time is open</b><BR><BR><hr>"; }

Now my question is, how can i modify it so that it also checks the $fldevent_end value?

say a meeting is already booked for 2:30 - 3:30 on a date
and someone wants to book a meeting for 2:00 - 3:00 on that same date?

Posted: Thu May 22, 2003 11:02 am
by liljester
thats odd, i ran that query before i posted.. it worked for me on mysql 4.0.12. anywho.

try something like this:

Code: Select all

<?php

$sql = "SELECT * FROM event WHERE ($fldevent_start BETWEEN event_start AND event_end) OR ($fldevent_end BETWEEN event_start AND event_end) AND roomid=1;";

?>

Posted: Thu May 22, 2003 2:14 pm
by WizyWyg
liljester wrote:thats odd, i ran that query before i posted.. it worked for me on mysql 4.0.12. anywho.

try something like this:

Code: Select all

<?php

$sql = "SELECT * FROM event WHERE ($fldevent_start BETWEEN event_start AND event_end) OR ($fldevent_end BETWEEN event_start AND event_end) AND roomid=1;";

?>
I re-tested it and worked this time around (your original code that is)

Muchos Mahalos Liljester. Forgot that you could use an or statement if you need to compare another value.

Now, I'll have to find a way of incorporating it into my existing code. If i run into problems, you'll probably see me ask again.

Posted: Thu May 22, 2003 2:51 pm
by liljester
no problem =)

okay, im stuck

Posted: Thu May 22, 2003 3:01 pm
by WizyWyg
Okay, I can't figure out how to use the new code. This will be a a little long

Code: Select all

// Defined functions
//--------------------------

function get_param($param_name)
{
  global $HTTP_POST_VARS;
  global $HTTP_GET_VARS;

  $param_value = "";
  if(isset($HTTP_POST_VARS[$param_name]))
    $param_value = $HTTP_POST_VARS[$param_name];
  else if(isset($HTTP_GET_VARS[$param_name]))
    $param_value = $HTTP_GET_VARS[$param_name];

  return $param_value;
}

function tosql($value, $type)
{
  if(!strlen($value))
    return "NULL";
  else
    if($type == "Number")
      return str_replace (",", ".", doubleval($value));
    else
    {
      if(get_magic_quotes_gpc() == 0)
      {
        $value = str_replace("'","''",$value);
        $value = str_replace("","\",$value);
      }
      else
      {
        $value = str_replace("\''","''",$value);
        $value = str_replace(""",""",$value);
      }

      return "'" . $value . "'";
    }
}


//===============================
// Action of the Record Form
//-------------------------------
function event_action($sAction)
{
  global $db;
  global $tpl;
  global $sForm;
  global $seventErr;
  $bExecSQL = true;
  $sActionFileName = "";
  $sParams = "?";
  $sWhere = "";
  $bErr = false;
  $pPKeventid = "";
  $fldevent_title = "";
  $fldevent_desc = "";
  $fldevent_start = "";
  $fldevent_end = "";
  $fldWNDate = "";
  $fldroomid = "";
  $fldteam = "";
  $flduserid = "";
  $fldtype = "";

// event CustomAction Event begin
//-------------------------------
  $sActionFileName = "admin_eventadd.php";
  $sParams .= "eventid=" . urlencode(get_param("Trn_eventid"));


// CANCEL action
//-------------------------------
  if($sAction == "cancel")
  {
	header("Location: admin_event.php");
    exit;
  }


// Build WHERE statement
//-------------------------------
  if($sAction == "update" || $sAction == "delete") 
  {
    $pPKeventid = get_param("PK_eventid");
    if( !strlen($pPKeventid)) return;
    $sWhere = "eventid=" . tosql($pPKeventid, "Number");
  }


// Load all form fields into variables
//-------------------------------
  $fldevent_title = get_param("event_title");
  $fldevent_desc = get_param("event_desc");
  $fldevent_start = get_param("event_start");
  $fldevent_end = get_param("event_end");
  $fldWNDate = get_param("WNDate");
  $fldroomid = get_param("roomid");
  $fldteam = get_param("team");
  $flduserid = get_param("userid");
  $fldtype = get_param("type");

// Validate fields
//-------------------------------
  if($sAction == "insert" || $sAction == "update") 
  {
    if(!strlen($fldevent_title))
      $seventErr .= "The value in field Name is required.<br>";
    
    if(!strlen($fldevent_desc))
      $seventErr .= "The value in field Description is required.<br>";
    
    if(!strlen($fldevent_start))
      $seventErr .= "The value in field Start is required.<br>";
    
    if(!strlen($fldevent_end))
      $seventErr .= "The value in field End is required.<br>";
    
    if(!strlen($fldWNDate))
      $seventErr .= "The value in field Date is required.<br>";
    
    if(!strlen($fldroomid))
      $seventErr .= "The value in field Room is required.<br>";
    
    if(!strlen($fldteam))
      $seventErr .= "The value in field Team is required.<br>";
    
    if(!strlen($flduserid))
      $seventErr .= "The value in field Posted By is required.<br>";
    
    if(!is_number($fldroomid))
      $seventErr .= "The value in field Room is incorrect.<br>";
    
    if(!is_number($fldteam))
      $seventErr .= "The value in field Team is incorrect.<br>";
    
    if(!is_number($flduserid))
      $seventErr .= "The value in field Posted By is incorrect.<br>";
    
    if(strlen($seventErr)) return;
  }

// Create SQL statement
//-------------------------------
  switch(strtolower($sAction)) 
  {
    case "insert":
	$formated_datetime = "$fldWNDate $fldevent_start";
	$fldevent_start = strtotime($formated_datetime);

	$formated_datetime2 = "$fldWNDate $fldevent_end";
	$fldevent_end = strtotime($formated_datetime2);

	 if ($sSQL == "")
      {
        $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") . 
          ")";
      }
    break;
    case "update":
	
	$formated_datetime = "$fldWNDate $fldevent_start";
	$fldevent_start = strtotime($formated_datetime);

	$formated_datetime2 = "$fldWNDate $fldevent_end";
	$fldevent_end = strtotime($formated_datetime2);

      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 ();
$sParams .= $last; 

// event AfterInsert Event end

    break;
  }
  header("Location: " . $sActionFileName . $sParams);
  exit;
//-------------------------------
// event CustomAction Event end
//-------------------------------
}


I know that my new code will have to be:

Code: Select all

$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);
so that it can return a result.
So essentially, how can I get it to do this:

Code: Select all

if(mysql_num_rows($result) == 0) 
{ 
     mysql_query("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 { $error_message = "There is already an event already scheduled. Please pick a different time and/or room"; }
And make sure it re-displays the form?

Posted: Thu May 22, 2003 3:16 pm
by liljester
are you using ezSQL or some sort of Database abstraction layer?

Posted: Thu May 22, 2003 3:33 pm
by WizyWyg
liljester wrote:are you using ezSQL or some sort of Database abstraction layer?
what is that?

And I just realized that I forgot another piece in the code above; the $db value.

Code: Select all

// Database Initialize
$db = new DB_Sql();
$db->Database = DATABASE_NAME;
$db->User     = DATABASE_USER;
$db->Password = DATABASE_PASSWORD;
$db->Host     = DATABASE_HOST;

Posted: Thu May 22, 2003 3:38 pm
by liljester
*scratches head*

is DB_Sql() a class or function you dont have listed?

Posted: Thu May 22, 2003 4:44 pm
by WizyWyg
sorry, its a class to open a connection to the mysql database.

Do you need it in its entirety to understand? (its in its own file and is then included into the page)

Posted: Thu May 22, 2003 8:33 pm
by WizyWyg
Okay, i tried this:

Code: Select all

//-------------------------------
// 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 no matter what I input, it displays the error message. Even if the "time " is open/free.

Posted: Fri May 23, 2003 10:41 am
by liljester
you're going to have to read the info on the database class you are using (if you didnt write it yourself)... what you want is to be able to see how many rows, if any, you query returns. if the query does return some rows then you know that there is a conflict