Need help with a query/ mysql and php insert

[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:

$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 would conflict...

 

[sleipnir214]

Just from a user-interface issue, I would have the user select the room he wants to reserve, then have the script show all current reservations for that room. Your users could get frustrated if they have to guess for a free slot two or three times.

From a database view, I would just use columns of type datetime -- they're a lot more human-readable (for debugging purposes) and MySQL can perform sorts and comparisons on columns of that type. You can use PHP's date() function to provide dates like you need to insert into PHP. PHP's strtotime() function understands the date/time format MySQL uses in order to perform date calculations in PHP.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[WizyWyg]

Cant go back change the structure. Im using Unix Time stamps because its just easier when I need to do repeatable events.

This is what i have so far just to test:

$db_name = "reserve";
$db_pw = "root";
$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.");

//$sql = "SELECT * FROM event WHERE (DAYOFWEEK(event_date)=2 AND WEEK(NOW()))";
//SELECT * FROM event WHERE (DAYNAME(event_date)='Monday' AND TO_DAYS(event_date)- TO_DAYS(NOW()) <=1)
//SELECT 1 FROM table WHERE time BETWEEN start AND end
$fldevent_start = &quot;1053459000&quot;;
$fldevent_end = &quot;1053460800&quot;;
$roomid = &quot;2&quot;;
// start time of original event 1053457200
$sql = &quot;SELECT * FROM event WHERE $fldevent_start BETWEEN 1053457200 AND 1053460800 AND roomid=$roomid&quot;;
$result = mysql_query($sql,$connection);

if(mysql_num_rows($result))
{ echo &quot;<b>time is booked</b>&quot;; }
else
{ echo &quot;<b>time is open</b>&quot;; }

Which when executed, returns the "time is booked" message (that is correct)

But when i run the select as:

SELECT * FROM event WHERE $fldevent_start BETWEEN event_start AND event_end AND roomid=$roomid

It returns "Time is open".
(that is wrong)

event_start and event_end are the columns in the table event that are in UNIX time stamps.

How do i constuct the SELECT QUEry?

 

[DRJ478]

Here's what I noticed:

1. In your manual comparison you are comparing integers.
2. The second example you give uses 1 variable $fldevent_start which is an integer and 2 colums that are from the database.
Right? Or did you just forget to type $event_start and $event_end?

3. If not, is there a record in the database that fulfills your criteria?

4. What happens when you compare the integer $fldevent_start to timestamps?

expr BETWEEN min AND max
    If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place, according to the rules above, but applied to all the three arguments. Note that before 4.0.5 arguments were converted to the type of expr instead.

http://www.mysql.com/doc/en/Comparison_Operators.html

 

[WizyWyg]

I got it to work actually, just had to fix something in the DB

$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"; // 1053248400
$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 &quot;<b>time is booked</b><BR><BR><hr>&quot;; }
else
{ echo &quot;<b>time is open</b><BR><BR><hr>&quot;; }

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?

 

[DRJ478]

All you need to look for is:

1. check for all events that start between the proposed start and end times.

2. check for all events that end between the porposed start and end times

 

[WizyWyg]

Okay, didn't answer my question however; how do you construct the query?

 

[sej0924]

sleipnir's point was a good one. You wouldn't have to change the DB structure. Just have the user pick the room they want to reserve and for what day, and show them a schedule of the room for that day, and let them pick the time span they want, or choose a different day.

As it is, the user won't know what the problem with their request is, and will have to try numerous times on a day that is possibly totally booked up. Several airline reservation systems are like this online - they are very frustrating and I usually end up booking thru a different airline.

This shouldn't involve that much changing of your code, and will make it much more user-friendly.

s

 

[DRJ478]

There's really nothing difficult about the query. I thought you could take a stab at it; anyway, here it is:

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

Instead of looking for all fields I'd pick one, like if you have an event_id and just do a count:

SELECT count(event_id) AS num FROM event WHERE ($fldevent_start BETWEEN event_start AND event_end) OR ($fldevent_end BETWEEN event_start AND event_end) AND roomid=1

Then get the row and find out if num>0;