时间戳

首页 > 解决方案 > 我用于项目搜索的 sql 查询不起作用

php - 我用于项目搜索的 sql 查询不起作用

问题描述

我正在建立一个餐厅网站，我必须根据用户在搜索栏中输入的内容来搜索食物。我必须按最相关的结果对结果进行排序

更新：

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

我已经重新格式化了最新的 sql 语句并执行了查询，但它仍然没有给我任何想要的输出。现在我应该如何继续？

标签： phpmysqlsqlweb

解决方案

首先，不要在字符串中使用变量。您正在使用 PDO，因此，请改用准备好的语句。

另外，如果我理解正确，您需要对正在搜索的字符串使用“LOWER”函数吗？在这种情况下，使用 PHP“strtolower”函数一次并将该变量提供给 SQL 语句

$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();

已编辑

如果你想循环结果替换

$results = $statement->fetchAll();

和：

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

如果你不能或不想使用准备好的声明，那么这样做：

$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;
}

推荐阅读