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

UPDATE:

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

回答1:

Here's how I'd do it.

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

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;

Output:

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%')

回答2:

You should use OR (||) instead of AND (&&) . As it is, your search term must match against all fields:

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