2

I'm new to mysql, so im probably just not aware of how to properly set something like this up.

Basically each row in my table is a different food. I have columns for basic things like description...

But, I have a "ingredients" array, and "how much" array, in an array.

Basically on my end I can go ingredientsArray[0] howMuch[0] to display the ingredient and how much of it.

So, Id like to save both of these arrays in a cell for the row of the food.

I heard I could serialize the array, and save it. But i need it to be searchable.

(also, there is no set limit to how many ingredients. so i cant like have a column for each thats why im going the array route)

edit: 1 more thing incase this might help...howMuch will never be anything other than an int 0 1 2 3 4 5 6 7 8 9

What is the best way to go about this?

I'm doing my programming in AS3, and usng AMFPHP to connect to Mysql

brybam
  • 5,009
  • 12
  • 51
  • 93

4 Answers4

2

Create a new table of possible ingredients. Then create another table that will link your food table and a bunch of ingredients with a quantity.

YOUR_FOOD_TABLE
food_id (PK)
description
...

FOOD_INGREDIENTS
food_id (FK)
ingredient_id (FK)
quantity

INGREDIENTS
ingredient_id (PK)
ingredient_name
Aaron W.
  • 9,254
  • 2
  • 34
  • 45
  • what is the PK and FK? also, the `FOOD_INGREDIENTS` table...is that like somehow linked to the `ingredients` table? – brybam Mar 02 '12 at 21:56
  • `PK` is primary key and `FK` is foreign key. The `FOOD_INGREDIENTS` table links the two together by using the `food_id` and `ingredient_id` columns along with storing the `quantity` for the relation – Aaron W. Mar 02 '12 at 22:05
1

It's a lot better to have an ingredients table containing a column (food_id) that references the id of the record in the food table, and columns for the ingredient name and quantity.

Then you can search on the ingredients table, and get the ids of all the foods that contain that particular ingredient

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • wouldn't it be inefficient on my search query, for example on the foods table when i do a search, ill be performing it on table `foods` then have to do like a LEFT JOIN to the ingredients table, then ill be searching through that table with a LOT of ingredients to go though – brybam Mar 02 '12 at 21:53
  • 1
    It won't be inefficient if its properly indexed, it'll be a lot more efficient than storing an array. Searching is what databases do well... use them properly, and they'll make things very easy for you.... abuse them, and you'll make things very difficult for yourself. – Mark Baker Mar 02 '12 at 21:55
1

Serializing would still be text searchable, but the real solution here is to break out ingredients into a different table, and join them on a separate table including the quantity.

SuperRod
  • 557
  • 3
  • 8
0

use mysql FIND_IN_SET

Please escape the array values before creating this query

$insert_qry = 'INSERT INTO `table` (`ingredients`,`howmuch`) VALUES ("'.implode(',',$ingredientsArray).'", "'.implode(',',$howMuch).'")';

$search_qry = 'SELECT * FROM `table` WHERE FIND_IN_SET("bread",`ingredients`);';

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Fabrizio
  • 3,734
  • 2
  • 29
  • 32
  • Keep in mind that I agree with everybody else, the best solution is creating a secondary table and use `JOIN` – Fabrizio Mar 02 '12 at 21:59