"Pantry Search" query/loop.

Right, I'm trying with some difficulty to get a script to fetch food that you can make from items inside of your pantry.

So these here are the ingredients you have available to you:

<?php
    $itemsInPantry = array('bread', 'ham', 'cheese', 'mustard');
?>

PHP:

This here is the database table "recipes".

===id===    |===name===      |===ingredients=== 
1           | Ham Sandwich | ham, bread
2           | Melted cheese | cheese
3           | H&C Sandwich| ham, cheese, bread
4           | BLT                | Bacon, lettuce, tomato

Code (markup):

Now I want to run a query that searches for all the recipes you can make using the ingredients in the array - which are ham, cheese, bread and mustard. So the query should return:

Ham Sandwich, Melted Cheese and H&C Sandwich.

I can't grasp the logic of this one in my head. I've played around with a few queries and some complex looping procedures which seem unnecessary.

Thank you to anyone that can help.

 

There is one major problem in your database design, it is not normalized. If you do not know what normalization is, I suggest that you learn that as soon as possible. In short, your database structure must be changed to look something like this:

recipes table

===id===    |===name===
1           | Ham Sandwich
2           | Melted cheese
3           | H&C Sandwich
4           | BLT

ingredients table

===recipe_id===|===ingredient===
1              | ham
1              | bread
2              | cheese
3              | ham
3              | cheese
3              | bread
4              | bacon
4              | lettuce
4              | tomato

Code (markup):

Actually this structure should also be normalized more but I guess this should be enough for your case.

 

I've decided to help you with the query so:

SELECT recipe_id, COUNT(ingredients.ingredient), COUNT(pantry.ingredient)
FROM ingredients
LEFT JOIN (
SELECT 'bread' AS ingredient UNION
SELECT 'ham' UNION
SELECT 'cheese' UNION
SELECT 'mustard'
) AS pantry ON ingredients.ingredient = pantry.ingredient
GROUP BY recipe_id

Code (markup):

Add the following to the above query to find the recipes whose ingredients are fully available.

HAVING COUNT(ingredients.ingredient) = COUNT(pantry.ingredient)

Code (markup):

 

It is somewhat, though its not the most optimized database you'll find. I do have another table for each ingredient containing flavours etc etc. But since each ingredient occurs in multiple recipes (and I'm talking thousands) and each ingredient has so many details about it as well as script reasons, your approach is not viable. Well it could be made to work, but would cause more redundancy in the database design than there currently is. Your way definitely has its advantages, but because of the way the script is used, joining tables is something I'd really like to avoid.

That said I am not a database or SQL expert, which is why I was hoping for one or more simple queries that could then be processed in PHP to obtain the results.
If there's no other way I'll understand but if there is any other approach I would very much like to here them.

Thank you very much for your assistance

 

The thing I told you about normalization and breaking your table into two was to REDUCE redundancy, not add it. Placing items as a comma separated lists is probably not the right approach and you cannot use any sensible query to retrieve the results. But anyway:

You can try fetching ingredients column of all rows one by one in an array; explode it using comma as a separator; then use PHP functions such as array_intersect or any other function that performs set arithmetic over two arrays. I sure hope you know what sets are. You need to check if items in set A (the recipe you fetched from database) are wholly present in set B (the pantry).

 

Yeah I know what normalization is and I don't think the approach you originally provided is an ideal way to optimize the database of the script I'm working with. The example I originally posted was just an example - the ingredients aren't stored as comma lists but as serialized arrays and the structure is a bit more flexible to work with.

The approach you outlined above just then is what I was thinking of doing but I was hoping there was a more efficient way and I'm sure I'll find one. I just need to be able to map it out in my head.

So thank you very much for your help, you've been great, however I think the best solution in this case is just to sleep on it

 

Now that you mention that you're using serialized arrays, you can just do a:

$result = array_intersect($recipe, $ingredients_in_hand);
if(count($result) == count($recipe)) {
// go ahead and prepare the meal
}

PHP:

 

-_-

10charlimit.