Advanced ORDER BY when searching

Question

We have made a search field where you can search for multiple ingredients and find recipes.

We would like to sort the recipes according to the recipe with most ingredients from the search box.

if (isset($_POST['search'])) {
$searchquery = $_POST['search'];

$vals = "'" . str_replace(",", "','", $searchquery) . "'";

$query = mysql_query("SELECT * FROM opskrifter WHERE id IN 
(SELECT opskrifterid FROM ingredienser WHERE ing_name IN ('$vals'))") or die("search failed");

Is it possible to sort them?

EDIT: Recipe-table

+---------+----------+-------------+------------+------------+--+
|   id    | name     |  procedure  |  category  |  image_url |  |
+---------+----------+-------------+------------+------------+--+
|       1 | Sausage  | Fry it      | Main dish  | www....com |  |
|       2 | Pizza    | Bake it     | Main dish  | www....com |  |
|       3 | Burger   | Eat it      | Main dish  | www....com |  |
+---------+----------+-------------+------------+------------+--+

Ingredient-table

+---------+----------+-------------+------------+------------+--+
|   id    | recipeid |  ing_num    |  ing_meas  |   ing_name |  |
+---------+----------+-------------+------------+------------+--+
|       1 | 1        | 1           | stack      | sausage    |  |
|       2 | 2        | 200         | g          | wheat      |  |
|       3 | 2        | 100         | g          | beef       |  |
+---------+----------+-------------+------------+------------+--+

UPDATE

I've tried implementing the solution from Beginner/Raymond:

"SELECT *, COUNT(*) as `total_ingredients`
  FROM opskrifter as k
     , ingredienser as i
 WHERE k.id = i.opskrifterid 
   AND i.ing_name IN ($vals)
   GROUP BY k.id
 ORDER BY COUNT(*) DESC"

Where $vals = "'" . str_replace(",", "', '", $searchquery) . "'"; and $searchsquery = $_POST['search']; //From the searchfield

Unfortunately the search only takes the first word into account, example: "salt, pasta" it shows every recipe containing salt. But the recipe containing both ingredients is not the top sorted one.

What did I miss?

please provide your table structure, sample data and the desired output for more clearer explanation for the viewers — Beginner, Jan 09 '17 at 15:47
What you're searching for is 'relevance' where you order by the most matched terms. Something similar to http://stackoverflow.com/questions/18725941/mysql-order-by-best-match — Jay Blanchard, Jan 09 '17 at 15:49

score 1 · Answer 1 · edited Jan 09 '17 at 19:51

1

The answer below before me just missed a GROUP BY that's why it only returns one row

SELECT k.id
     , k.name
     , COUNT(*) as `total_ingredients`
  FROM receipts as k
     , ingredients as i
 WHERE k.id = i.receipt_id 
   AND i.ing_name IN ('sausage','beef', 'wheat', 'sauce', 'flour', 'wheat', 'beef', 'ketsup', 'onion', 'garlic')
   GROUP BY k.id, k.name
 ORDER BY COUNT(*) DESC;

JS Fiddle Here

edited Jan 09 '17 at 19:51

Raymond Nijland

11,488
2
22
34

answered Jan 09 '17 at 17:01

Beginner

4,118
3
17
26

@Raymond I've tried implementing your solution, take a look above :) – Jacob Jan 20 '17 at 10:33

score 0 · Answer 2 · answered Jan 09 '17 at 16:08

0

Jacob!

I think this query can solve your problem. Please, try it.

SELECT k.id
     , k.name
     , COUNT(*) 
  FROM opskrifter k
     , ingredienser i
 WHERE k.id = i.opskrifterid 
   AND i.ing_name IN ('sausage','beef', 'wheat')
 ORDER BY COUNT(*) DESC

answered Jan 09 '17 at 16:08

Pastor

318
1
11

unfortunately, it only shows one recipe, when ordering by count :( – Jacob Jan 09 '17 at 16:29

Advanced ORDER BY when searching

2 Answers2