SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL join and php search help

    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.

  2. #2
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    David

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  4. #4
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    Devon, UK
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 )

    Code:
    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.
    David


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •