www.webdeveloper.com
Results 1 to 2 of 2

Thread: New to SQL - How do I assign multiple categories?

Hybrid View

  1. #1
    Join Date
    May 2003
    Location
    Florida
    Posts
    38

    New to SQL - How do I assign multiple categories?

    Okay I am new to SQL and when I'm in phpmyadmin, the only data types I know how to use when creating my tables is VARCHAR, INT, TINYINT, and TXT and this is probably why I'm having difficulty with such a simple problem or perhaps there's just a better way of doing it.

    what I did in my SQL was create a table called categories and inside that table I have a list of all my categories, (i.e. breakfast recipes, lunch recipes, dinner recipes, onlet recipes, oat meal recipes, red meat recipes, sandwich recipes, etc).

    I also have another table which contains a list of all my recipes and one of the field names in that table is "categorie_id" and this is how I would assign categories to all my recipes. The problem is most, if not all of my recipes can fit into multiple categories for example, my "Greek Pork Tenderloin" recipe would fit into the Red Meat, Lunch, Greek, and Pork recipes categories. so my question is how could I make it possible to assign multiple categories to my recipes, videos, etc.
    Jawaune Saad

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    You probably want 3 tables: "recipe", "category", and "recipe_to_category". Hopefully "recipe" is obvious, "category" would simply be a primary key and whatever other fields you want to define a category, and then "recipe_to_category" would be -- at a minimum -- just 2 columns: the primary key of a row in "recipe" and the primary key of a row in "category" to which you want to associate that recipe (and probably want a unique index across those 2 fields).

    Then if you want, say, all recipes from category "Italian", your query might be something like:
    Code:
    SELECT col_1, col_2,...col_n
    FROM recipe
    INNER JOIN recipe_to_category ON recipes.recipe_id = recipe_to_category.recipe_id
    INNER JOIN category ON recipe_to_category.category_id = category.category_id
    WHERE category.name = 'Italian'
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles