Geo-location of Services and Appointment Setting. How to solve this puzzle?

[Popgun]

Hey all I have a design/solution dilemma I'm hoping someone may have encountered this before and or might point me in the right direction or give me advice on how to set this up.

 

THE SET UP

My global structure is:

 

Members Table

Location Table

Service Table

Appointments Table 

 

InnoDB Engine

 

I am using MySql 5.0.45 PhP 5

 

 

Issue: The design of the site allows users to input their information, (members table) then search for a service provider near them, and set up an the next available appointment (appointment table) with a service provider (who also is a member of site via the services table).

 

Obviously all of these have the appropriate primary and foreign key relationships mapped but for purposes of clarity, structure is like this:

 

MEMBERS

member_ID (Primary Key)

location_id (F Key)

 

LOCATION

location_ID (Primary Key)

 

SERVICE

service_ID (Primary Key)

location_id (F Key)

 

APPOINTMENT

appointment_ID (Primary Key)

member_id (F Key)

service_id (F Key)

 

There are other tables keyed but this is the basic structure.

 

Location table includes these columns: City, State, Zip, Lat, Long.

Members Table also includes a column for: State (this isn't a normalization flaw its for design reasons)

 

ISSUE:

Functionality:

 

User must be able to conduct a search in a radius of 5, 10, 20, 50 miles of their location

Result should be displayed using a map (Is Google Maps API best for this?)

Query should also result in a list of Service providers with next available appointment that the member can then select. (this forwards them to the appointment setting function/form)

 

 

So far I mapped this using pseudo code:

 

SELECT MEMBERS

WHERE member_ID AND location_id AND state = user

AND THEN

SELECT SERVICE

WHERE service_ID AND location_id = service providers (multiple)

IF within specified radius display

Else Exclude

AND display next available service provider appointment

AND allow user to select appointment

 

So I could use some advice, on how to get this done with SQL and PHP. This is a major feature of my site so I want to make sure I get it right.

 

Any advice would be appreciated