带有OR或AND或全文的表中的MySQL搜索关键字与[duplicate]匹配

This question already has an answer here:

这个问题在这里已有答案:

mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource 31 answers

mysql_fetch_array()/ mysql_fetch_assoc()/ mysql_fetch_row()/ mysql_num_rows等...期望参数1为资源31答案

I have a search form to search products in MySQL, the table PRODUCTS contain:

我有一个搜索表单来搜索MySQL中的产品,表格包含:

1) id

2) barcode

3) product_name

4) manufacturer

5) model

I have some more fields like price taxes etc but they are not important right now.

我有更多的领域,如价格税等,但它们现在并不重要。

With the follow code I am searching the database and it works almost fine but now there is a problem. Here is the code

使用以下代码我正在搜索数据库,它工作得很好,但现在有一个问题。这是代码

First the HTML form

首先是HTML表单

<form method="post" name="search">
<input type="text"  name="keywords" value="...">
<input type="submit"  value="search">
</form>

Now the PHP and MySQL code

现在是PHP和MySQL代码

$searchterms= mysqli_real_escape_string($database,$_POST['keywords']);
$result=mysqli_query($db,"
    SELECT * FROM `products` 
WHERE `barcode` LIKE '%$searchterms%'  
OR `product_name` LIKE '%$searchterms%' 
OR `model` LIKE '%$searchterms%'  
OR `manufacturer` LIKE '%$searchterms%'
");
$rows=mysqli_num_rows($result);
if(mysqli_num_rows($result)>0){
?>

I tried full text search but I get error

我尝试了全文搜索,但是我收到错误

    $searchterms=        mysqli_real_escape_string($database,$_POST['keywords']);
$result=mysqli_query($database,"SELECT * FROM products
    WHERE MATCH (product,manufacturer,model)
AGAINST ('$searchterms' IN NATURAL LANGUAGE MODE);");
    $rows=mysqli_num_rows($result);
    if(mysqli_num_rows($result)>0){
    ?>

Error text Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in

错误文本警告:mysqli_num_rows()期望参数1为mysqli_result,布尔值为

As you can see and understand, I have a single input text to search in the database.

正如您所看到和理解的那样,我只需要在数据库中搜索一个输入文本。

If I search for keyword: "Samsung" then I get the products from manufacturer Samsung.

如果我搜索关键字:“三星”然后我从制造商三星获得产品。

If I search for model "S6 Edge" then I get the products with model S6 or S6 edge

如果我搜索“S6 Edge”型号,那么我会得到带有S6或S6型号的产品

BUT

If I use 2 words keywords " samsung s6 " the search result is empty

如果我使用2个词的关键字“samsung s6”,那么搜索结果为空

Any idea why?

知道为什么吗?

EDIT:

Here is the full code I am using in a Fiddle

这是我在小提琴中使用的完整代码

https://jsfiddle.net/psnf0arw/

It will be easier to understand looking at the PHP code complete

查看完整的PHP代码会更容易理解

2 个解决方案

#1

If your intention is to find any of the keywords in any of those fields you could try using REGEXP instead of LIKE.

如果您打算在任何这些字段中找到任何关键字,可以尝试使用REGEXP而不是LIKE。

$searchterms = mysqli_real_escape_string($database,$_POST['keywords']);
$searchexp = implode("|", str_word_count($searchterms, 1));
$result=mysqli_query($db,"
    SELECT * FROM `products` 
        WHERE `barcode` REGEXP '$searchexp'
        OR `product_name` REGEXP '$searchexp'
        OR `model` REGEXP '$searchexp'
        OR `manufacturer` REGEXP '$searchexp'
");

#2

First of all. When you use a specific model name the query will be setted up like that:

首先。当您使用特定的型号名称时,查询将如下设置:

SELECT * FROM products WHERE barcode LIKE '%samsung s6%' OR product_name LIKE '%samsung s6%' OR model LIKE '%samsung s6%' OR manufacturer LIKE '%samsung s6%'

SELECT * FROM产品WHERE条形码LIKE'%samsung s6%'或product_name LIKE'%samsung s6%'或型号LIKE'%samsung s6%'或制造商LIKE'%samsung s6%'

that will return nothing, because there´s no matching model name equal to 'samsung s6'. The code handles the search criteria like as one single string 'samsung s6' , and if doesnt matches doesnt return anything.

什么都不会返回,因为没有匹配的模型名称等于'samsung s6'。代码处理搜索条件,如单个字符串'samsung s6',如果不匹配则不返回任何内容。

#1