搜索确切的关键字 [英] Search exact keywords

问题描述

我正在尝试创建一个搜索脚本，以搜索数据库中的汽车并匹配用户输入的所有关键字.如果我将关键字文本框留空并进行搜索，则会得到结果，但是如果输入任何关键字，都不会得到结果.

I am trying to make a search script that searches cars in a database and matches ALL keywords input by user. If I leave keywords text box empty and search I get results but if I input any keywords I get no results.

$search_keywords = $_POST['search_keywords'];
$terms =  $search_keywords;

$items = explode(' ',$terms);
$types = array();
    foreach ($items as $item) {
        $types[] = "'title' LIKE '%{$item}%'";
        $types[] = "'exterior_colour' LIKE '%{$item}%'";
    }

    $sql = "SELECT * FROM list_car WHERE ";
    $sql .= implode(" && ", $types) . " ORDER BY 'title'";

    $result = mysqli_query($link, getPagingQuery($sql, $rowsPerPage));

更新:

现在可以更改它了，但是如果我搜索Toyota hilux dfkjodsgfudsugfdsgfgfgfdgfdg，将显示所有Toyota hilux，但是dfkjodsgfudsugfdsgfgfgfdgfdg是垃圾邮件，数据库中未列出该垃圾，我希望它与所有关键字匹配，而不仅仅是一个或多个. /p>

Works now i have changed it but if i search Toyota hilux dfkjodsgfudsugfdsgfgfgfdgfdg all the Toyota hilux will appear but dfkjodsgfudsugfdsgfgfgfdgfdg is garbage which is not listed in the database i want it to match ALL keywords not just one or more.

$search_keywords = $_POST['search_keywords'];
$terms =  $search_keywords;

$items = explode(' ',$terms);
$types = array();
    foreach ($items as $item) {
        $types[] = "`title` LIKE '%{$item}%'";
        $types[] = "`exterior_colour` LIKE '%{$item}%'";
    }


    $sql = "SELECT * FROM list_CAR WHERE ";
    $sql .= implode(" || ", $types) . "";

    $result = mysqli_query($link, getPagingQuery($sql, $rowsPerPage)) or die(mysqli_error($link));

推荐答案

这是我的处理方式.

$terms = $search_keywords = 'Toyota hilux dfkjodsgfudsugfdsgfgfgfdgfdg';
$items = explode(' ',$terms);
$types = array();
$sql = "SELECT * FROM list_CAR WHERE ";
foreach ($items as $item) {
    $sql .= " (`title` LIKE ? or `exterior_colour` LIKE ?) and ";
    $params[] = '%' . $item . '%';
    $params[] = '%' . $item . '%';
}
if(!empty($params)) {
     $sql = rtrim($sql, ' and ');
     $result = mysqli_prepare($link, $sql);
     foreach($params as $param) {
         mysqli_stmt_bind_param($result, "s", $param);
     }
     mysqli_stmt_execute($result);
} else {
     die('No params built...WHY');
}

请注意，我使用的是未经测试的mysqli预处理语句，我没有在mysqli中按程序构建参数化查询，我将这种方法基于

Note I'm using untested mysqli prepared statements, I haven't built the parameterized queries procedurally in mysqli, I base this approach off user comments on the manual's page.

这应该给出一个查询，例如

This should give a query such as

SELECT * FROM list_CAR 
WHERE  
(`title` LIKE ? or `exterior_colour` LIKE ?) and  
(`title` LIKE ? or `exterior_colour` LIKE ?) and  
(`title` LIKE ? or `exterior_colour` LIKE ?)

需要在标题或颜色列表中出现每个关键字.

Which will require each keyword is present in the title or the color list.

如果您不准备它，这是不推荐的做法，并且做法不当，那就应该这样做.

If you were to keep it unprepared, which is unrecommended and poor practice, it would be..

$terms = $search_keywords = 'Toyota hilux dfkjodsgfudsugfdsgfgfgfdgfdg';
$items = explode(' ',$terms);
$types = array();
    foreach ($items as $item) {
        $types[] = " (`title` LIKE '%{$item}%' or `exterior_colour` LIKE '%{$item}%') ";
    }
    $sql = "SELECT * FROM list_CAR WHERE ";
    $sql .= implode(" and ", $types) . "";
echo $sql;

输出:

SELECT * FROM list_CAR WHERE  
(`title` LIKE '%Toyota%' or `exterior_colour` LIKE '%Toyota%')  and  
(`title` LIKE '%hilux%' or `exterior_colour` LIKE '%hilux%')  and 
 (`title` LIKE '%dfkjodsgfudsugfdsgfgfgfdgfdg%' or `exterior_colour` LIKE '%dfkjodsgfudsugfdsgfgfgfdgfdg%')

这篇关于搜索确切的关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！