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.

leatherback

Personally, I would never use the same colum name in a database. Ever. But I seem to be the only one on this forum that holds that opinion. Anyway>. Having unique colum names solves a lot of minor headaches, such as the one you are facing now.

Your query would work like this:

select restaurant.ID as res_id, restaurantscuisine.id as ResC_id from Restaurant
left join restaurantscuisine on res_id = resc_id

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!