Simple PHP search in Database won't bring up result

Question

I am building a recipe database with some of my friends and for that we need our users to be able to search within our site. Our database consists of 3 tables:

recipes - recipe_id (primary key), recipe_name

ingredients - ingredient_id (primary key), ingredient_name

recipe_ingredients - ingredient_id (foreign key), recipe_id (foreign key)

We want to be able to search the recipe_ingredients for a recipe or ingredient name and have our site show every ingredient connected to that recipe or every recipe connected to that ingredient. And so we made this query:

select ingredient_name, recipe_name, recipe_ingredients.*
from recipe_ingredients
inner join ingredients inner join recipes
on recipe_ingredients.ingredient_id = ingredients.ingredient_id
and recipe_ingredients.recipe_id = recipes.recipe_id

WHERE ingredient_name = 'Brød';

Which works fine for us. However, putting it into our search function in php, it gives 'There were no search results!' back every single time no matter what we searched. Here is the code. Would someone point out the mistake we made?

$output = '';
if (isset($_POST['work'])) {
 $searchq = $_POST['work'];
 $searchq = preg_replace("#[^0-9a-z]#i","",$searchq);
 $query = mysql_query 

  ("select ingredient_name, recipe_name, recipe_ingredients.*
  from recipe_ingredients
  inner join ingredients inner join recipes
  on recipe_ingredients.ingredient_id = ingredients.ingredient_id
  and recipe_ingredients.recipe_id = recipes.recipe_id
  WHERE ingredient_name LIKE '%searchq%' or recipe_name LIKE '%searchq%'")

 or die ("Could not search");
 $count = mysql_num_rows($query);
 if($count == 0){
    $output = 'There were no search results!';
 }
 else{
  while ($row = mysql_fetch_array($query)) {
   $recipe = $row[recipe_name];
   $ingredient = $row[ingredient_name];
   $id = $row[ingredient_id];

   $output .= '<div>'.$recipe.' '.$ingredient.'</div>';
  }
 }
}

We don't understand why it won't work.

STOP using deprecated mysql_* API use mysqli_* or PDO with prepared statements — Jens
Can you give me an example where I have done this? I have looked over the code so many times I feel blind when looking at it. And thanks for your time. — Nenn
@user2304993: In your SQL string. Additionally, you forgot to quote the indexers when fetching data from $row. — David
Example = ingredient_name LIKE '%searchq%' should be ingredient_name LIKE '%$searchq% — RiggsFolly

Kostas Mitsarakis Kostas Mitsarakis · Accepted Answer · 2015-10-08T14:44:34

You can try the following. It uses mysqli_* functions and a better structure for the query joins.

$connection = mysqli_connect('localhost', 'root', 'your_password', 'your_database');
mysqli_set_charset($connection, 'utf8');
if (!$connection) {
    die("Database connection failed: " . mysqli_error());
}

$output = '';
if (isset($_POST['work'])) {

    $searchq = $_POST['work'];
    $searchq = preg_replace("#[^0-9a-z]#i", "", $searchq);

    $sql = "
        SELECT ingredient_name, recipe_name, recipe_ingredients.*
        FROM recipe_ingredients
        INNER JOIN ingredients
        ON recipe_ingredients.ingredient_id = ingredients.ingredient_id
        INNER JOIN recipes
        ON recipe_ingredients.recipe_id = recipes.recipe_id
        WHERE ingredient_name LIKE '%$searchq%' or recipe_name LIKE '%$searchq%'";

    $result = mysqli_query($connection, $sql);
    if (!$result) {
        die("SQL Error: " . mysqli_error($connection);
    }

     $count = mysqli_num_rows($result);

     if ($count == 0) {
        $output = 'There were no search results!';
     } else {
        while ($row = mysqli_fetch_array($result)) {
            $recipe = $row[recipe_name];
            $ingredient = $row[ingredient_name];
            $id = $row[ingredient_id];

            $output .= '<div>'.$recipe.' '.$ingredient.'</div>';
        }
    }
}

Simple PHP search in Database won't bring up result

3 Answers