Help with writing MySQL query or queries

Question

I'm trying to write a function in PHP who's job it is to check a date and time against these tables and determine if it's possible to schedule an appointment.

Tables:

locations
---
id

appointment_types
---
id
length_of_time (not sure what to store this as, but at the moment I would like it to be precise to the quarter hour)

partners
---
id

partners_details
---
partner_id
location_id

partners_appointment_types
---
partner_id
appointment_type_id

appointments
---
id
datetime
partner_id
appointment_type_id
location_id

I have the following info:

• Date
• Time
• Type of appointment (appointment types differ in length of time)
• Location

So far, this is how I've been looking at the problem:

Scheduling an appointment

1. Who are the available partners for this type of appointment at this location
2. Is the part available at this date and time?

I don't know how to accomplish this through intelligent use of MySQL queries. The original is a mess of PHP.

Can someone at least point me in the right direction?

EDIT

Thanks to CFreak, this is what I have so far. Once I added the appointments JOIN, I stopped getting results. I don't know how to add that in properly.

SELECT
    p.partnerid
FROM
    partners AS p
LEFT JOIN
    partner_appointment_types AS pat ON pat.partner_id = p.partnerid
LEFT JOIN
    partners_details AS pd ON pd.id = p.partnerid
JOIN
    appointment_types AS at ON at.id = pat.appointment_type_id
JOIN
    appointments AS cp ON cp.partnerid = p.partnerid
WHERE
    pat.appointment_type_id = 1
AND
    pd.location_id = 1
AND
    (
        (cp.datetime + INTERVAL at.length_of_time MINUTE) <= '2010-10-28 14:30:00'
        OR
        cp.datetime >= ('2010-10-28 14:30:00' + INTERVAL at.length_of_time MINUTE)
    )

Answer 1

The length of time could be stored as an integer in minutes

This should work but I haven't tested it:

select partner_id FROM appointments JOIN appointment_type ON appointment_type_id=appointment_type.id WHERE appointment_type_id=$appointment_type AND ( (datetime + INTERVAL appointment_type.length_of_time MINUTE) <= $datetime  OR datetime >= ($datetime + INTERVAL appointment_type.length_of_time MINUTE) )

The variables I've used are examples. The $datetime will need to be in ISO format that Mysql uses (YYYY-MM-DD HH:mm:SS)

You should make sure they are properly escaped or better yet use a data abstraction layer that provides placeholders