New to SQL - How do I assign multiple categories?

[jawaune]

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.

[NogDog]

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'