MySQL join and php search help

[vzwhaley]

Hello everyone and thanks much for your past help. I would like some guidance on how to set up one SQL statement using joins for four MySQL tables.

I am creating a restaurant guide and have four tables - Restaurants, RestaurantsCuisine, RestaurantsFeatures and RestaurantsLoc.

The Restaurants table holds all of the details for the restaurant itself. The RestaurantsCuisine table holds a list of several different cuisines, such as American, Mexican, Greek, etc. The RestaurantsFeatures table holds a list of different restaurant features, such as Full Bar, All You Can Eat, Wireless Internet Access, etc. The RestaurantsLoc table holds a list of cities that the restaurants can be located in.

I am wondering how I can combine all four tables using joins to load several dropdown lists to be used as jump menus for a search. Each restaurant can have multiple cuisines and features, so should I just create an array of the cuisines and features using LIKE '%".$search_array."%' in the SQL statement?

Some of the schema of the Restaurants table:
ID
City
Cuisine
Features

The RestaurantsCuisine table:
ID
CuisineType

The RestaurantsFeatures table:
ID
FeatureType

The RestaurantsLoc table:
ID
Loc

The Restaurants table can be joined to these tables using:
Restaurants.Cuisine = RestaurantsCuisine.CuisineType
Restaurants.Features = RestaurantsFeatures.FeaturesType
Restaurants.City = RestaurantsLoc.Loc

I just don't know how to form the joins in the SQL statement and still be able to use the IDs for each table separately. Should I rename the ID fields for the cuisine, features and loc tables so I can pull in the actual ID numbers separately in separate dropdown menus?

Any help with all of these questions would be much appreciated. Thank you in advance.

[ClickHeRe]

I usually use qualifiers in front of all my fields in a table to avoid name clashing

users table

user_id | user_name | user_...

and when I have a foreign key relation in another table for example

table books:

book_id | user_id | book_title, | book_...

It's easy to knw from which table the foreign key comes from and how to join them in a query for example. All my columns are thus unique.

[vzwhaley]

I have changed the names of the ID fields in the cuisines, features and loc tables to CuisineID, FeaturesID and LocID. What would be the best way to create the SQL statement and joins now? Thanks for your help!

[ceeb]

If you don't want to rename your fields, make sure you fully qualify a field using it's table, e.g.

Restaurants.ID, RestaurantsCuisine.ID, RestaurantsLoc.ID, etc.

But, I need to ask why you're using so many tables for something this simple? If the RestaurantsCuisine table only has one ID (the restuarant) and a single CuisineType, then it'd be more efficient to have CuisineType in the main Restaurants table.

[vzwhaley]

thanks, ceeb.

actually, we have multiple entries for both cuisines and features and am storing them in the restaurants table in an array. so i will probably have to continue using separate SQL statements.

[ClickHeRe]

if you have a many to many relationship in your tables, you should implement this like this:

restaurants table:
restaurant_id | other fields

locations table:
location_id | other fields

features table:
feature_id | other fields

cuisines table:
cuisine_id | other fields


Now the mapping of the restaurants to other stuff is done like this

restaurant2location table:
restaurant_id | location_id

restaurant2feature table:
restaurant_id | feature_id

restaurant2cuisine table:
restaurant_id | cuisine_id

Modeled like this any restaurant can have any number of features, locations, cuisines. You can now retrieve all the information you want with simple joins using the restaurant_id with 2 tables ( restaurant2XXX + XXX )

SELECT whatever_fields_you_want FROM XXX AS t1, resturant2XXX AS t2 WHERE XXX.XXX_id=restaurant2XXX.restaurant_id AND restaurant2XXX.restaurant_id = 'ID'

Where XXX = location(s) or feature(s) or cuisine(s) <-- (s) for the table names only!

You can probably get all the information of a single restaurant in a single very big query but at least your information is properly normalized in the DB and you can do it with 3 small queries as a subset.