I'm looking for someone who can write a mysql query for me.
I'm developing a recipe site and am looking for a way to select recipes from a database and order them based on how many of the recipe's ingredients match the ingredients of the user's pantry table.
I have four tables in my database
id, name, prep_time
ingredient_types (static list of all ingredients in the world)
ingredients (for each recipe posted a record is created for the ingredient needed)
id, ingredient_type_id, recipe_id
pantry (a table containing records of each ingredient a user has at home)
I was hoping to pull recipe data using fulltext search and in the same query check how many matches each ingredient of the recipe has to the user's pantry table and then order the output of the recipe data based on the number of ingredient matches.
Query must be