Sorry for the second reply here.
I read somewhere on the forum that you were looking for a way to allow your users to select recipes based on selected ingredients. Where the list of user selected ingredients could be more than one.
Assuming that you have organisede the database like this.
Table 1: Recipes Fields RecipeID,[Field2],[Field3]
Table 2: Ingredients Fields IngredientID,Name Of Ingredient
Table 3: RecipeIngredient Fields RecipeID,IngredientID
Table 3 being what I call a link table but maybe called a transaction table or somesuch.
Then you need to use SubQueries to get a list of recipes using the ingredients. Example for the above below:
SELECT *
from RecipeIngredient
where IngredientID = 1 IN
(
select RecipeID
from RecipeIngredient
where IngredientID = 2
);
This will return a list of recipeID's of recipes that include both Ingredients 1 and 2. It will also return an IngredientID which will always be 1 which is of no use. Then use a simple main query on your database based on the list returned such as :
SELECT * FROM Recipes WHERE RecipeID = [Value returned from Query 1]
This is of course simplistic as you will at that time want to return the whole list of ingredients so would use something like
SELECT Recipes.RecipeID,Title,RecipeIngredient.Amount,Ingredients.IngredientID,Ingredients.Name
FROM Recipes
LEFT OUTER JOIN RecipeIngredient on Recipes.RecipeID = RecipeIngredient.RecipeID
LEFT OUTER JOIN Ingredients ON Ingredients.IngredientID = RecipeIngredient.IngredientID
followed by the where clause.
This should speed it up a bit as the recipeID field could be indexed. Indices being I believe the way to get speed from a database.
You also mention images. I also found that storing the actual image in the database to create a really bloated database so I store a 150 X 150 or 200 X 200 thumbnail in the database along with the filename of the original image. This allows me to show a simple image in the display window and then print the original image from the filename. Printing an image from a filename is one of the capabilities of the CDPrint module.
If any SQL experts can improve the above please let me know as I have a project running which needs to do a similar thing.
Hope this helps a little.
Regards
cd
PS If you prefer a small coded example I can knock one up for you as I had to do a little of the code to get this far?
)