My sql query for item search is not working

Im building a restaurant website and i have to search for food items according to what the user inputs in the search bar. I have to sort the results by the most relevant one

UPDATED:

if (isset($_POST['searchValue']))
{
function test_input($data) {
 $data = trim($data); //whitespacess
 $data = stripslashes($data); //removes backslashes n clean data from 
   database or form
   $data = htmlspecialchars($data); //converts predefined characters to html 
  entities, encoding user input so that they cannot manipulate html codes
  return $data;
  }



    $inpText=$_POST['searchValue'];

    $searchData=test_input($inpText);

    $starString= strtolower('{$searchData}');
    $searchData = $conn->quote($starString);
    $searchDataStartMatch = $conn->quote("%".$starString);
    $searchDataEndMatch = $conn->quote($starString."%");
    $searchDataBothMatch = $conn->quote("%".$starString."%");


    $sql="SELECT * FROM food WHERE Food_Name like {$searchDataBothMatch} OR 
         Food_Description LIKE {$searchDataBothMatch}
          ORDER BY CASE WHEN Food_Name={$searchData} or Food_Description = 
                    {$searchData} THEN 0
                        WHEN Food_Name like {$searchDataEndMatch} or 
                        Food_Description LIKE {$searchDataEndMatch} THEN 1
                        WHEN Food_Name like {$searchDataBothMatch} or 
                        Food_Description LIKE {$searchDataBothMatch} THEN 2
                        WHEN Food_Name like {$searchDataStartMatch} or 
               Food_Description LIKE {$searchDataStartMatch} THEN 3
                 ELSE 4
          END";

    $res=$conn->query($sql);



    if ($res->rowCount()>0)
    {
        while($row=$res->fetch(PDO::FETCH_ASSOC))
        {
            ?>

            <div class="col-lg-3 mx-0">
            //the code continues here 

I have re formatted the latest sql statement and have executed the query but still it does not give me any desired output.. Now how shall i proceed?

Answer

First of all, don’t use variables in the string. You are using PDO, therefore, use prepared statements instead.

Also if I understood correctly you need the “LOWER” function for the string you are searching right? in that case, use PHP “strtolower” function once and give that variable to SQL statement

$searchData= strtolower("{Some_text_to_search}");
$searchDataStartMatch = "%".$searchData;
$searchDataEndMatch = $searchData."%";
$searchDataBothMatch = "%".$searchData."%";
$sql="SELECT * FROM food WHERE Food_Name like ? OR Food_Description LIKE ?
      ORDER BY CASE WHEN Food_Name=? or Food_Description = ? THEN 0
                    WHEN Food_Name like ? or Food_Description LIKE ? THEN 1
                    WHEN Food_Name like ? or Food_Description LIKE ? THEN 2
                    WHEN Food_Name like ? or Food_Description LIKE ? THEN 3
             ELSE 4
      END";
$statement=$conn->prepare($sql);
$statement->execute([
    $searchDataBothMatch,$searchDataBothMatch
    $searchData,$searchData,
    $searchDataEndMatch,$searchDataEndMatch,
    $searchDataBothMatch,$searchDataBothMatch,
    $searchDataStartMatch,$searchDataStartMatch
]);
$results = $statement->fetchAll();

edited

if you want to loop over results replace

$results = $statement->fetchAll();

with:

$result = [];
while($row = $statement->fetch()){
    // your logic here
    $result[] = $row;
}

If you Cannot or dont want to use prepared statemants then do so:

$starString= strtolower("{Some_text_to_search}");
$searchData = $conn->quote($starString);
$searchDataStartMatch = $conn->quote("%".$starString);
$searchDataEndMatch = $conn->quote($starString."%");
$searchDataBothMatch = $conn->quote("%".$starString."%");
$sql="SELECT * FROM food WHERE Food_Name like {$searchDataBothMatch} OR Food_Description LIKE {$searchDataBothMatch}
      ORDER BY CASE WHEN Food_Name={$searchData} or Food_Description = {$searchData} THEN 0
                    WHEN Food_Name like {$searchDataEndMatch} or Food_Description LIKE {$searchDataEndMatch} THEN 1
                    WHEN Food_Name like {$searchDataBothMatch} or Food_Description LIKE {$searchDataBothMatch} THEN 2
                    WHEN Food_Name like {$searchDataStartMatch} or Food_Description LIKE {$searchDataStartMatch} THEN 3
             ELSE 4
      END";
$statement=$conn->query($sql);

$result = [];
while($row = $statement->fetch()){
    // your logic here
    $result[] = $row;
}