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
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
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
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.
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