Storing arrays of Data in Fields

[danjacko]

Hi Guys,

 

I'm developing a script for a friend and have run into a problem. I'm trying to make a system whereby the user enters ingredients (like keywords or tags) and the system will output recipes based on the input. In the database I was planning to make it look something like:

 

ID (Primary) | Recipe  |  Ingredients

 

1                  cake        flour, sugar

 

 

and when the user enters ingredients such as flour, the script will return will cake. Using VARCHAR wouldn't work because querying

 

SELECT Recipe FROM $table WHERE Ingredients = flour

 

wouldn't return any results because there is no row where Ingredients are equal to just flour. and what about if the user inputted 'sugar, flour' the database wouldn't recognize them because 'sugar, flour' != 'flour, sugar'. So any easy way of doing this would be if the data were arrays of data in one field.

 

One thing I was thinking was to have about 20 columns and each ingredient in a separate column then for a php script to scan down every column until it finds a matching ingredient. Then it would log that ingredient and if all the ingredients are matched it returns a positive match on a recipe. However this idea is really long winded and would take hours to code. It would also be quite hard for a user to interact with it so does anyone have any better ideas?

 

 

Many Thanks

 

[The Little Guy]

make two tables:

 

- table 1 stores the item, such as: cake, pizza, muffin, doughnut, etc. Each item would then have a unique key.

- table 2 stores the ingredient info for each item.

 

table_1:

id | name
1  | cake
2  | pizza
3  | muffin
4  | doughnut

 

 

table_2:

id | itm_id | ingredient | amt | measurement
1  | 1      | flour      | 0.5 | cup
2  | 1      | sugar      | 1   | TSP
3  | 2      | cheese     | 3   | cup
4  | 2      | sauce      | 2   | cup

 

Now lets say you want to get the ingredients for a pizza

 

<?php
$sql = mysql_query("select * from table_1 left join table_2 on(table_1.id = table_2.itm_id) where table_1.id = 2");
while($row = mysql_fetch_assoc($sql)){
print_r($row);
}
?>

 

You could also do this (closer to your question):

 

<?php
$sql = mysql_query("select * from table_1 left join table_2 on(table_1.id = table_2.itm_id) where table_2.ingredient like '%flour%'");
while($row = mysql_fetch_assoc($sql)){
print_r($row);
}
?>

 

Hope this helps!

[fenway]

+1