CODEWITHSUNDEEP
phpsqlcodeigniter
Veeraj Rathore
Veeraj Rathore

Reputation: 1

How to get multiple value from one sql column

I have 2 tables one is dishes and the other is ingredients. First site will display all the dishes but when a user click on the onion checkbox then the query will display items which contain onion. If user click on potato checkbox then the query will display items which contain onion AND potato and so on. it will filter down the results according to the user inputs. It would be awesome if anybody can help. 

SELECT *
FROM (`Ingredients`)
WHERE `category` IN ('1', '8') 
AND `ingredients_name` = 'onion'
AND `ingredients_name` = 'potato'
AND `ingredients_name` = 'cream ';

Thanks

Upvotes: 0

Views: 127

Answers (4)

landons
landons

Reputation: 9547

I think you would need a separate join per ingredient, building the query dynamically.

Example:

SELECT a.*
FROM `Dishes` a
JOIN `Ingredients` i1 ON i1.dish_id = a.id AND i1.ingredients_name = 'onion'
JOIN `Ingredients` i2 ON i2.dish_id = a.id AND i2.ingredients_name = 'potato'
JOIN `Ingredients` i3 ON i3.dish_id = a.id AND i3.ingredients_name = 'cream'
WHERE a.category IN ('1', '8');

I'm making several assumptions here, but you should get the idea. Also, I would recommend using ingredient IDs, rather than strings (or, at the very least, put an index on ingredients_name).

Upvotes: 0

munch1324
munch1324

Reputation: 1148

What is the relationship between Dish and Ingredient (Many : 1, 1:1, etc)? If it is many to many something like:

 SELECT *
    FROM  Dish d
    JOIN DishIngredients di on di.dish_id = i.dish_id
    JOIN Ingredient i on i.ingredient_id = di.ingredient_id 
    WHERE d.`category` IN ('1', '8') 
    AND i.`ingredients_name` = 'onion'
    AND i.`ingredients_name` = 'potato'
    AND i.`ingredients_name` = 'cream ';

should provide the information. If it is a set number of FK, you can join on each FK to get the full constraint.

Upvotes: 0

leslieyuen
leslieyuen

Reputation: 1

I think you should add a Binary string column which contain all ingredients info for dishes eg, 1 for onion, 10 for potato, 100 for cream ,1000 for ⋯⋯

then if you want to find all dishes contain onion, potato, cream

use this sql

select * from dishes where meta_info like '111%' for onion, potato, cream use select * from dishes where meta_info like '1_1%' for onion and cream use select * from dishes where meta_info like '__1%' for cream

to solve it perfectly, think about lucene or sphinx

Upvotes: 0

user2363031
user2363031

Reputation: 21

If you want to select multiple values can you not use this:

SELECT *
FROM (`Ingredients`)
WHERE `category` = '1' OR  `category` = '8'
AND `ingredients_name` = 'onion'
AND `ingredients_name` = 'potato'
AND `ingredients_name` = 'cream ';

Upvotes: 1

Related Questions