I have a table that looks like so:
Menu_id | Dish | Ingredient
The dishes column is repeated in line with the number of ingredients. I want to find all the Dishes and then see if there are other Dish with similar ingredients, based on a percentage match. I have built the select and a while loop for each unique dish. Now how do I then search for other dishes inside this loop on matching ingredients?
Thanks,
D
Me, I would have the ingredients for each dish stored in an array with that dish. Since you're looping over the list of dishes, then within that loop you'd loop over the same list again so that you end up comparing every dish against every other dish [insert cleverness here to avoid wasting time comparing two dishes against each other twice, or comparing a dish against itself].
To find out the difference in ingredients between the two dishes then would then involve using array_intersect and/or array_diff to find which ingredients in one dish are/aren't in the other: bog-standard school-level set manipulation stuff.
How you calculate the percentage from that ... well, if they have identical lists of ingredients then their intersection has the same number of ingredients as both dishes, and if they don't have any ingredients in common the intersection is empty.
I'm assuming Ingredient doesn't include quantities, or that's a whole 'nother layer of complication.
It could probably be done via one or more DB queries, though I might need a few more details about the database schema (and might recommend some changes to optimize this). Whether that would perform better or worse than Weedpacket's method of using PHP's array functions is a total crap shoot at this point. 
I also had this crazy idea of using bit-wise comparisons by assigning each possible ingredient in the DB with a power of 2 index -- but that gets pretty crazy if you have lots of possible ingredients (for an undefined value of "lots" ).
Thanks to both of you, I will start to work on your suggestions Weedpacket.
D
