Results 1 to 2 of 2

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

  1. #1
    Join Date
    May 2003

    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
    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:
    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'
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

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