CODEWITHSUNDEEP
phpmysqlsqlsortingsql-order-by
Jacob
Jacob

Reputation: 31

Advanced ORDER BY when searching

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?

Upvotes: 3

Views: 150

Answers (2)

Beginner
Beginner

Reputation: 4153

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

Upvotes: 1

Pastor
Pastor

Reputation: 318

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

Upvotes: 0

Related Questions