Need help with a query

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

Need help with a query

Post 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.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post 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
Last edited by liljester on Thu May 22, 2003 3:12 pm, edited 1 time in total.
WizyWyg
Forum Commoner
Posts: 92
Joined: Tue Aug 06, 2002 7:20 pm

Post 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.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

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

Post 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?
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post 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;";

?>
WizyWyg
Forum Commoner
Posts: 92
Joined: Tue Aug 06, 2002 7:20 pm

Post 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.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

no problem =)
WizyWyg
Forum Commoner
Posts: 92
Joined: Tue Aug 06, 2002 7:20 pm

okay, im stuck

Post 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?
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

are you using ezSQL or some sort of Database abstraction layer?
WizyWyg
Forum Commoner
Posts: 92
Joined: Tue Aug 06, 2002 7:20 pm

Post 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;
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

*scratches head*

is DB_Sql() a class or function you dont have listed?
WizyWyg
Forum Commoner
Posts: 92
Joined: Tue Aug 06, 2002 7:20 pm

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

Post 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.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post 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
Post Reply