How to select a users specific info from a MySQL database?

Question

I was wondering how can I have my users pick their favorite food from a category of foods in my MySQL database.

Will I need two different tables? If so what will my second table look like?

Here is my MySQL food table structure.

id | parent_id | food | url

Answer 1

You'll need 3 tables in total:

1. Food - holds food information
2. Users - holds users information
3. Users_Food - holds user id + food id (and maybe a ranking)

You should probably read up on database normalization.

Answer 2

You'd need to make a second table:

user_id | food_id

Make them both primary. Then you can use JOIN's to select the food:

SELECT f.food, f.url
FROM user_food AS u 
INNER JOIN food AS f ON (f.id = u.food_id)
WHERE u.user_id = {USER_ID}

This will give you a list of all favorite foods set by the user.