date comparisons

az_kiwi

My events table stores events with their start and end date.

I want to be able to check whether a new event will conflict with one already present in the database - this could be anytime within the date period (start-end).

thankx...

tomhath

What part of the manual's explanation don't you understand?

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

az_kiwi

hmmm, still doesnt really answer my question - maybe I was unclear.

Say I have a conference room booked from 1st of Feb until the 7th of Feb, with these being the start and end dates in a table 'booking'.

Now if someone where to try and insert a new booking say starting on the 5th of Feb how do I check this - as no room can be double booked.

thanks

nnichols

SELECT * FROM tbl_name WHERE date BETWEEN start AND end;

If it returns data then don't let them enter it

Hope this helps 😉

nnichols

Something like this -

// date to check availability for
$date = 2003-01-23;

$result = mysql_query("SELECT * FROM bookings WHERE $date BETWEEN start AND end");

if (mysql_num_rows($result) != 0) {
	echo "Can\'t have that date";
	}
else {
	// do insert
	}

az_kiwi

cheers for that,
If I am right this query will look to see whether a given date (I could do this for both start and end) falls between any other start and end date record in the database.

The only prob I see now is what if I had a booking in the db that was from the 6th - 9th of feb and someone tries to add a booking from the 5th - 11th?

xblue

okay, it should be better to check if an existing start date falls within the new time period or an existing end date does, I think that should catch all cases...

// date to check availability for
$new_start = 2003-01-23;
$new_end=2003-01-29;

// I don't think the parenthesis are needed but may be nicer to look at
$result = mysql_query("SELECT * FROM table_name WHERE (start_column_name BETWEEN '$new_start' AND '$new_end') OR (end_column_name BETWEEN '$new_start' AND '$new_end')");

if (mysql_num_rows($result) != 0) {
    echo "Can't have that date";
    }
else {
    // do insert
    }

az_kiwi

awesome,
I think now that if I check whether a existing start date (and end date) falls between the new start and end date AND whether the new start date (and end date) falls between an existing start and end date I will catch all...

😃
sweet