Sql for varying where condition

I am developing a recipe site using codeigniter (i am using mysql as database). User can enter any number of ingredi开发者_StackOverflow中文版ents.I need to search recipes which contain these ingredients. i.e if user puts eggs and tomatoes i need to search recipes which uses both.recipe Search result should be order by less number of extra ingredient required. Since the number of ingredient varies how to write sql query? How to order search result? Any idea of how to go about it? Thank you in advance.

The simplest solution (not the best, IMHO, but one of the easiest) is to utilize MySQL's fulltext index. You can start by reading MySQL's documentation on fulltext index, it's tremendously useful in making a little search engine. Borrowing explanation from wikipedia:

In a full text search, the search engine examines all of the words in every stored document as it tries to match search words supplied by the user.

So, in my suggestion, you can have this table similar to this:

----------------------------------------
RECIPES
----------------------------------------
id          | int(11)      | PRIMARY KEY
title       | varchar(255) |
body        | mediumtext   |
ingredients | tinytext     | FULLTEXT

Considerations:

• Add fulltext index to ingredients field only.

• Store ingredients inside the field ingredients, separated with space, don't use comma.

• Store ingredients in two places, the ingredients field above is just for searching. You might be better off with another table to store ingredient list for other use cases.

• Since you're using fulltext index, you can use BOOLEAN MODE syntax. If you want to search for recipes that has egg and tomato use +eggs +tomato, if you want to search for recipes that contains egg but not tomato, use +egg -tomato. See MySQL's manual for detailed explanation.

Again, it might not be the best solution, but it's the first thing that I can think of, and it's certainly easy.

A couple of caveats:

• MySQL's fulltext engine doesn't recognize words with lower than 4 characters by default, you can change this.

• If you don't use boolean mode, MySQL fulltext search will return empty if the result if more than 50% of your dataset.

• Fulltext search will ignore stop words, here's the list.

http://www.jarrodgoddard.com/web-development/advanced-web-site-search-with-sql here's an article on how to use MySQL Queries to get relevance-based search results. With a little bit of creativity you can add relevance yourself by ordering them by the amount of a certain ingredient.

How are ingredients saved? In a relational table I hope (recipe_id, ingredient_id, amount)? That makes it easier to search.