Couting matched keywords in query

[leatherback]

Hi All,

I run a recipe website (www.cookseasonal.com). I would like to allow visitors to search for recipies based on specific ingredients. That is easy enough. However, I would like to be able to sort the recipes based on the number of ingredients that match the requested list.

Say someone enters: potatoes, Onions, Carrot, Chicken, then I would like to order recipes with 4 matches first, then with 3 out of 4 ingredients etc.

How would I go about this?

My database setup:

LINKTABLE.itr_re_nr => Holds the recipe number
LINKTABLE.itr_in_nr => holds the ingredient number

Normal query to retreive one recipe:

Select [FIELDS] from from [LINKTABLE] 
   left join [INGREDIENTTABLE] on itr_in_nr = in_id 
   left join [RECIPETABLE] on itr_re_nr = re_id 
   where in_name = "potatoe" 
   order by itr_sort ASC

(The tablenames in caps have been adjusted to avoid showing the rest of the world the database setup).

[m_tt]

Can't you just toss in a COUNT() on an ingredient then GROUP BY recipe?

[m_tt]

It's hard to give an example without having the actual query to work with but something like this:

SELECT a.recipe_id, b.ingredient_id, COUNT(b.ingredient_id) AS num_ingredients FROM recipes AS a LEFT JOIN ingredients AS b ON a.recipe_id = b.recipe_id GROUP BY a.recipe_id ORDER BY num_ingredients DESC