我如何在Mysql数据库中搜索多字搜索

> here is the code.

>这是代码。

i want to search the every word in my database but the results only show with single keyword not the multiple. it is a PDO database when i search single word like facebook then the result is appear if i search two or more word search then the result is not appear.

我想搜索我的数据库中的每个单词,但结果只显示单个关键字而不是倍数。它是一个PDO数据库,当我搜索像facebook这样的单个单词时,如果我搜索两个或多个单词搜索然后结果没有出现,则会出现结果。

function getResults(){
 $q=$GLOBALS['q'];
 $p=$GLOBALS['p'];
 $start=($p-1)*10;
 if($p!=null){
  $starttime = microtime(true);
  $sql=$GLOBALS['dbh']->prepare('SELECT title, url, description FROM search WHERE `title` LIKE :q OR `url` LIKE :q OR `description` LIKE :q ');
  $sql->bindValue(":q", "%$q%");
  $sql->execute();
  $trs=$sql->fetchAll(PDO::FETCH_ASSOC);
  $endtime = microtime(true);
  if($sql->rowCount()==0 || $start>$sql->rowCount()){
   return 0;
  }else{
   $duration = $endtime - $starttime;
   $res=array();
   $res['count']=$sql->rowCount();
   $res['time']=round($duration, 4);
   $limitedResults=array_slice($trs, $start, 12);
   foreach($limitedResults as $r){
    $res["results"][]=array($r['title'], $r['url'], $r['description']);
   }
   return $res;
  }
 }
}
?>

3 个解决方案

#1

-1

here is complete script you found helpful.

这里是您发现有用的完整脚本。

 <?php  
                 if(isset($_GET["search"]))  
                 {  
                      $condition = '';  
                      //$query = explode(" ", $_GET["search"]);
                      $query = explode(" ", $_GET["search"]);

                      foreach($query as $text)  
                      {  
                           $condition .= "`title`  LIKE +'%".mysqli_real_escape_string($connect, $text)."%' OR ";  
                      }  
                      $condition = substr($condition, 0, -4);  
                      $sql_query = "SELECT * FROM countries2 WHERE " . $condition;  
                      $result = mysqli_query($connect, $sql_query);  
                      if(mysqli_num_rows($result) > 0)  
                      {  
                           while($row = mysqli_fetch_array($result))  
                           {  
                                echo '<tr><td>'.$row["title"].'</td></tr>';  
                           }  
                      }  
                      else  
                      {  
                           echo '<label>Data not Found</label>';  
                      }  
                 }  
                 ?>

#2

Not sure why you didn't use @david strachan 's approach (which I think is a very good solution), but I will try to explain an option you might also use and understand why you're not getting any result currently.

不知道为什么你没有使用@david strachan的方法(我认为这是一个非常好的解决方案),但我会尝试解释你可能也会使用的一个选项,并理解为什么你目前没有得到任何结果。

At this point what happens is, you're sending these values to your query which eventually becomes;

在这一点上发生的是,你将这些值发送到你的查询,最终成为;

SELECT title, url, description FROM search WHERE `title` LIKE `google+facebook+yahoo` OR `url` LIKE `google+facebook+yahoo` OR `description` LIKE `google+facebook+yahoo`

I don't think you want to look for a tittle containing all of these.

我认为你不想寻找含有所有这些的小东西。

I'm not sure how to write this using a query but a quick solution for you could be like this.

我不确定如何使用查询来编写此内容,但是对您来说快速解决方案可能就是这样。

Insert the values into an array (google+facebook+yahoo)

将值插入数组(google + facebook + yahoo)

use a loop to select each key from the array created and pass that to the query

使用循环从创建的数组中选择每个键并将其传递给查询

make the query add it's result as an array

使查询将其结果添加为数组

#3

You will have to build a dynamic query to search for two words. In the code below I illustrate building query using GET for showing query and array. I use unnamed parameters ? and "lazy" binding when possible - passing data into execute for ease.

您必须构建一个动态查询来搜索两个单词。在下面的代码中,我使用GET来说明构建查询以显示查询和数组。我使用未命名的参数?和可能的“懒惰”绑定 - 将数据传递给执行以方便。

//Using GET here for testing

<?php
//Using GET here for testing
if(isset($_GET['q'])){
$q = $_GET['q'];
//}
//First split string
if (strpos($q, ' ') > 0) {
$pieces = explode(" ", $q);
//echo count($pieces);  
//You need an array to store columns
$columnArray = array( "title", "url", "description");
//You need an array to store parameters
$paramArray =array();
$clause = "WHERE";
}
//You need to build a dynamic query to perform this. Start with a basic stub

    $sql = "SELECT `title`, `url`, `description` FROM search ";

//Start building the query
//Also see PDO WIKI for use % in placeholders.ie placeholders cannot represent an arbitrary part of the query, but a complete data literal only
//Loop through columns
        foreach ($columnArray  as $column) {
            //$sql .
            foreach ($pieces as $value) {
                $sql .= " $clause $column LIKE ?";
            $clause = "OR";
            array_push($paramArray,"%$value%");
            }
} 

//echo query and parameter array
echo $sql;
echo "<br>";  
print_r($paramArray);

//Prepare and execute query 
//  $sth = $db->prepare($sql);
//  $sth->execute($paramArray);
}
?>

Result from xxx.php?q=google amazon quora or xxx.php?q=google+amazon+quora

结果来自xxx.php?q = google amazon quora或xxx.php?q = google + amazon + quora

    SELECT `title`, `url`, `description` FROM search WHERE title LIKE ? OR title LIKE ? OR title LIKE ? OR url LIKE ? OR url LIKE ? OR url LIKE ? OR description LIKE ? OR description LIKE ? OR description LIKE ?
Array ( [0] => %google% [1] => %amazon% [2] => %quora% [3] => %google% [4] => %amazon% [5] => %quora% [6] => %google% [7] => %amazon% [8] => %quora% )

#1

-1