problems with if isset php

Question

I am building a search for a web page that should list recipes which does not contain the ingredients listed (user can enter from one to four ingredients, only the first is required).

I cannot post images so I will try to show in text.

Search fields (user can enter from one to four ingredients, only the first is required)

---

Banner: RECIPE MISSING THESE INGREDIENTS.

Text Field for ingredient1 (required): vanilla

Text Field for ingredient2 (optional): beef

Text Field for ingredient3 (optional): tuna

Text Field for ingredient4 (optional): duck

Search Button

---

<?php

    //CONNECT TO DATABASE
    include_once ("dbc.php");

    //DECLAIR SESSION USERNAME
    $username=$_SESSION['username'];
    $getMemberID = mysql_query ("select member_id from members where member_username = '$username'");
    $memberID = mysql_result($getMemberID,0);

    //GET RECIPES WITHOUT INGREDIENT
    if (isset($_GET['ingredient1'])) {

        $ingredient1=$_GET['ingredient1'];
        //GET RECIPES FROM DATABASE
        $getAllRecipes = mysql_query ("select recipes.recipe_id, recipes.member_id, recipes.recipe_name as 'recipeName', cooking_time.cooking_time as 'cookingTime', concat(member_name, ' ', member_surname) as 'author' from members left join recipes ON members.member_id = recipes.member_id left join cooking_time ON recipes.cooking_time_id = cooking_time.cooking_time_id where recipes.recipe_ingredients not like '%$ingredient1%'");

    } else if (isset($_GET['ingredient1'], $_GET['ingredient2'])){

        $ingredient1=$_GET['ingredient1'];
        $ingredient2=$_GET['ingredient2'];
        //GET RECIPES FROM DATABASE
        $getAllRecipes = mysql_query ("select recipes.recipe_id, recipes.member_id, recipes.recipe_name as 'recipeName', cooking_time.cooking_time as 'cookingTime', concat(member_name, ' ', member_surname) as 'author' from members left join recipes ON members.member_id = recipes.member_id left join cooking_time ON recipes.cooking_time_id = cooking_time.cooking_time_id where recipes.recipe_ingredients not like '%$ingredient1%' and recipes.recipe_ingredients not like '%$ingredient2%'");

    } else if (isset($_GET['ingredient1'], $_GET['ingredient2'], $_GET['ingredient3'])) {

        $ingredient1=$_GET['ingredient1'];
        $ingredient2=$_GET['ingredient2'];
        $ingredient3=$_GET['ingredient3'];
        //GET RECIPES FROM DATABASE
        $getAllRecipes = mysql_query ("select recipes.recipe_id, recipes.member_id, recipes.recipe_name as 'recipeName', cooking_time.cooking_time as 'cookingTime', concat(member_name, ' ', member_surname) as 'author' from members left join recipes ON members.member_id = recipes.member_id left join cooking_time ON recipes.cooking_time_id = cooking_time.cooking_time_id where recipes.recipe_ingredients not like '%$ingredient1%' and recipes.recipe_ingredients not like '%$ingredient2%' and recipes.recipe_ingredients not like '%$ingredient3%'");

    } else if (isset($_GET['ingredient1'], $_GET['ingredient2'], $_GET['ingredient3'], $_GET['ingredient4'])) {

        $ingredient1=$_GET['ingredient1'];
        $ingredient2=$_GET['ingredient2'];
        $ingredient3=$_GET['ingredient3'];
        $ingredient4=$_GET['ingredient4'];
        //GET RECIPES FROM DATABASE
        $getAllRecipes = mysql_query ("select recipes.recipe_id, recipes.member_id, recipes.recipe_name as 'recipeName', cooking_time.cooking_time as 'cookingTime', concat(member_name, ' ', member_surname) as 'author' from members left join recipes ON members.member_id = recipes.member_id left join cooking_time ON recipes.cooking_time_id = cooking_time.cooking_time_id where recipes.recipe_ingredients not like '%$ingredient1%' and recipes.recipe_ingredients not like '%$ingredient2%' and recipes.recipe_ingredients not like '%$ingredient3%' and recipes.recipe_ingredients not like '%$ingredient4%'");

    } 

    //POPULATE TABLE ROWS WITH DATA FROM DATABASE
    while ($allRecipes = mysql_fetch_array ($getAllRecipes)) {

        //GET USERNAME TO USE FOR ENABLING MODIFY/DELETE    
        $dbMemberID = $allRecipes['member_id'];
        $getDbUsername = mysql_query ("select members.member_username from members where members.member_id = '$dbMemberID'");
        $dbUsername = mysql_result($getDbUsername,0);

        //CREATING TABLE ROWS WITH RECIPE INFORMATION
        echo "<tr>";
            echo "<td><a href='show_recipe.php?id=" . $allRecipes['recipe_id'] . "'>" . $allRecipes['recipeName'] . "<a></td>";         
            echo "<td>" . $allRecipes['cookingTime'] . "</td>";
            echo "<td>" . $allRecipes['author'] . "</td>";

            if ($username === $dbUsername) {
                echo "<td align='center'><a href='modify_recipe.php?id=" . $allRecipes['recipe_id'] . "'>Modify<a></td>";
                echo "<td align='center'><a href='delete_recipe.php?id=" . $allRecipes['recipe_id'] . "'>Delete<a></td>";
            } else {
                echo "<td></td>";
                echo "<td></td>";
            }

        echo "</tr>";

    }   
?>

The way it is now, only the first ingredient entered is being taken into consideration even though I have entered four ingredients (see below).

Search results displayed in web page (I am only showing recipe name):

---

RECIPE NAME

Rib Roast

Tuna sweet potato jackets

Macaroni cheese with bacon & pine nuts

Duck confit burger

Chilli con carne

Lamb & lettuce pan-fry

Tomato & onion salad

Triple cheese & aubergine lasagne

Pan-fried salmon with watercress, polenta croutons & capers

Oat Waffle

Kale and Portobello Lasagna

---

In the above, although I search for recipes not containing vanilla, beef, tuna and duck, I still got recipes containing beef, tuna and duck. I tried the same thing on workbench and received successful results:

    select 
    recipes.recipe_id,
    recipes.member_id,
    recipes.recipe_name as 'recipeName',
    cooking_time.cooking_time as 'cookingTime',
    concat(member_name, ' ', member_surname) as 'author'
from
    members
        left join
    recipes ON members.member_id = recipes.member_id
        left join
    cooking_time ON recipes.cooking_time_id = cooking_time.cooking_time_id
where
    recipes.recipe_ingredients not like '%Vanilla%'
        and recipes.recipe_ingredients not like '%beef%'
        and recipes.recipe_ingredients not like '%tuna%'
        and recipes.recipe_ingredients not like '%duck%';

Results from workbench (good):

---

recipeName

Macaroni cheese with bacon & pine nuts

Lamb & lettuce pan-fry

Tomato & onion salad

Triple cheese & aubergine lasagne

Pan-fried salmon with watercress, polenta croutons & capers

Oat Waffle

Kale and Portobello Lasagna

---

Anyone can help revise the php code?

Thanks in advance

Answer 1

Problem is in order of checks, let's say you get all 4 ingredients.

First check isset($_GET['ingredient1']) Ingedient1 is set? Sure! Then your sql-query generates and executes. No further checks are done.

You should rebuild your if statements from all ingredients to one only:

if (isset($_GET['i1'], $_GET['i2'], $_GET['i3'], $_GET['i4'])) {
    // do something
} else if (isset($_GET['i1'], $_GET['i2'], $_GET['i3'])) {
    // do something
}  else if (isset($_GET['i1'], $_GET['i2'])) {
    // do something
} else if (isset($_GET['i1'])) {
    // do something
}

Answer 2

You should use OR instead of AND, since it will now only look for things which don't have ALL of the given items (vanilla, beef, tuna, duck) any value in your DB that has just one,two or three of these will come up in your search.

Try this instead:

select 
recipes.recipe_id,
recipes.member_id,
recipes.recipe_name as 'recipeName',
cooking_time.cooking_time as 'cookingTime',
concat(member_name, ' ', member_surname) as 'author'
from
members
    left join
recipes ON members.member_id = recipes.member_id
    left join
cooking_time ON recipes.cooking_time_id = cooking_time.cooking_time_id
where
recipes.recipe_ingredients not like '%Vanilla%'
    OR recipes.recipe_ingredients not like '%beef%'
    OR recipes.recipe_ingredients not like '%tuna%'
    OR recipes.recipe_ingredients not like '%duck%';