Searching multiple values in sql table, with a range

Question

I have a table that contains some ingredients. Example:

 id | title | ingredients 
 1  | ex1   | ketchup, chicken, salt, honey, mountain dew
 2  | ex2   | beef, pepper, chili, honey, salt

And when the user searchs for the ingredients like:

ketchup, salt, honey

I generate a sql-query:

select * from recipes where( 
(ingredients LIKE '%ketchup%')
AND (ingredients LIKE '%salt%')
AND (ingredients LIKE '%honey%')

And it returns all recipes containing these specific ingredients, and it works grey.

Now. I've added a range-slider, to pick how many of the entered ingredients that should match for the search to return anything. Lets say i chose that 2 ingredients should match at least, i want to make a PHP function that outputs a sql string that pairs everyone of the entered ingredients, but i simply don't have the mindset for it. Example:

  (ingredients LIKE '%ketchup%') AND (ingredients LIKE '%salt%')
  OR 
  (ingredients LIKE '%ketchup%') AND (ingredients LIKE '%honey%')
  OR
  So on. So ketchup & salt pair, ketchup & honey pair, salt & honey pair.

And of course variable so theres no limit to the ingredients inputted. I've tried for hours but no success. Hope i've explained my self clearly & someone will be able to help or teach me something :-)

My php function that does the current string looks like this:

$c_soeg = "ketchup, whatever, whatever. etc";
$c_ing_arr = explode(",", $c_soeg);
$c_count_arr = count($c_ing_arr);
$i = 0;
$c_sql = "SELECT * FROM recipes WHERE (";
while($i < $c_count_arr){
    $c_sql .= "(ingredients LIKE '%".trim($c_ing_arr[$i])."%')";
    if($i != $c_count_arr-1){
        $c_sql .= " AND ";
    }
    $i++;
}
$c_sql .= ")";

And the variable that contains the value of the range is named

$c_range;

Stopped reading in 3rd line, when the comma separated values appeared. Don't do that. Read about normalization. — fancyPants
– fancyPants, Commented Apr 14, 2014 at 13:53
This was just a example, i know about normalization but does not solve the problem — Imbue
– Imbue, Commented Apr 14, 2014 at 13:55
@user2979822: Oh, it would. With a table of ingredients you would use the having clause and be done with the task. Moreover you wouldn't mix apple and pineapple with %apple%. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 14, 2014 at 14:02
@user2979822 You should have three tables (e.g. recipes, ingredients, recipes_ingredients). In the table that relates recipes to ingredients (recipe_ingredient) you could have a column for quantity and a column for unit. — Strawberry
– Strawberry, Commented Apr 14, 2014 at 14:11

Thorsten Kettner · Accepted Answer · 2014-04-14 13:54:30Z

2

Instead of AND and OR conditions count the met criteria. This example gives you all records where at least two ingredients match:

select * from recipes
where
  case when ingredients like '%ketchup%' then 1 else 0 end
  +
  case when ingredients like '%salt%' then 1 else 0 end
  +
  case when ingredients like '%honey%' then 1 else 0 end
  > = 2;

answered Apr 14, 2014 at 13:54

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MenzZana · Accepted Answer · 2014-04-14 14:07:03Z

2

I think you should make 3 tables meaning one for the title and another for the ingredients and one to connect them

    recipy
    id  | title |
     1  | ex1   | 
     3  | ex2   |

    recipyingredients
    recipyid | ingredientsid
           1 | 1
           1 | 2
           1 | 3
           1 | 4
           1 | 5
           2 | 1
           2 | 6
           2 | 7           

    ingredients
     id | ingredients 
     1  | ketchup
     2  | chicken
     3  | salt
     4  | honey
     5  | mountain dew
     6  | beef
     7  | pepper

In that case one recipy can have many ingredients and viceversa The database would be clearer and you would not have to use like % as much. Also you do not have to write the same ingredients every time for a recipy

answered Apr 14, 2014 at 14:07

MenzZana

1786 bronze badges

5 Comments

MenzZana Over a year ago

You can also add the amount of the ingredient in the recipyingredients table

Imbue Over a year ago

Thanks for reply. Problem is that its the user that writes the ingredients, like "200 bananas, 5 bottles of ketchup", and so on. Just think it would be really complicated to split all that up, into different tables. People have different ways of putting things too.

Racil Hilan Over a year ago

@user2979822 It will be probably a bit complicated, but definitely the right way to do things. Remember, that the right way always needs more work, but it pays off at the end. You need to validate the user input and decide what to allow/disallow them to type in. The problem you're facing now is the direct result of no taking control. You need to take ownership and control over your app and architecture.

Strawberry Over a year ago

A nice user interface can deal with those problems. This is a much more useful design.

MenzZana Over a year ago

That is always a problem to interpret what people write, but I am guessing that you make people input from a webpage? You could give them limited actions aka a controlled vocabulary. What I mean an amount text input, a drop-down menu what measure they can have (bottles, liters ...) and then a drop-down menu with the ingredients (which also can make them add new ingredients)

Collectives™ on Stack Overflow

Searching multiple values in sql table, with a range

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related