Determine Logic for MySQL Select - Reservation System

Question

Hi all,

I'm creating an equipment reservation (PHP/MySQL) for my school.

My database looks like this:

tblEvents:
 id
 start
 end
 equipID


tblEquipment:
  id
  name
  description

I'd like to know the best way to query the database based on a user's time parameters to see if a certain item is 'not available.'

For example, if I have the following data:

tblEvents:
  id      start          end         equipID
  1     1251312300    1251324000       1

And the user submits the query, looking to see if "equipID" 1 is available, and they put:

1251300300 as their start and 1251524000 as their end.

Any help on getting this started would be great. Thanks.

Answer 1

I'm not 100% sure on this, but I think this query should be pretty close:

select * from tblEvents
where (@starttime >= start && @starttime <= end) /* Starts while someone else has it */
or (@endtime >= start && @endtime <= end) /* Ends while someone else has it */
or (@startime <= start && @endtime >= end) /* Starts before & ends after someone else has it*/
and equipID = @equipID

Replace @starttime, @endtime & @equipID with the appropriate equipment id and start & end times entered by the user. If this query returns no results, then there should be no time conflicts.

Basicly it's checking for these 3 scenarios, where the * is your start & end time, the - is unused time, and the | is time its being used by someone else:

-|||||*|||||------*------
------*--------|||*||||--
------*--|||||||--*------

Answer 2

SELECT * FROM tblEvents
WHERE NOW() > FROM_UNIXTIME(end)