php - 在使用 php 搜索 Mysqli 数据库时遇到一些问题

Question

我在Mysqli + PHP搜索功能中遇到问题。我有一些记录有performerName = JOANNE GRACE SCHOOL OF DANCE ASome Records have JOANNE GRACE SCHOOLAnd Some have only JOANNE GRACE。

我想要什么，如果我用查询搜索。

$query = "JOANNE GRACE SCHOOL OF DANCE A";

SELECT * FROM `sections` WHERE `performerName` LIKE '%JOANNE GRACE SCHOOL OF DANCE A%'

我想从查询中获取所有匹配的单词 JOANNE GRACE，JOANNE GRACE SCHOOL 但是这个查询给出了所有的完整名称。 JOANNE GRACE SCHOOL OF DANCE A

我在 Mysql 中尝试了很多方法。它搜索我上面给出的整个查询。但我希望它应该匹配所有记录，即使某些记录有来自该查询的 2 个单词或来自该查询的 3 个单词。

我试过了REGEX，LIKE%%但没有奏效。它匹配完整查询。有什么解决办法吗

标签： phpmysql

Answer 1

我不确定是否有一个很好的解决方案可以让您的查询格式为：

SELECT * FROM `sections` WHERE `performerName` LIKE ?

但是，使用and匹配$performerName字符串中的任何单词是一项相当简单的任务。LIKEOR

mysqli

$performerName = "JOANNE GRACE SCHOOL OF DANCE A";                     // Input

$search_string = preg_replace("/(\w+)/i", "%$1%", $performerName);    // Surround each word with "%" ready for the LIKE clause
$search_array  = explode(" ", $search_string);                        // Split input into array of search terms

$option_type = str_repeat("s", count($search_array));                 // Count how many search terms there are and output the value types for mysqli->bind_param
$option_list = implode(" OR ", array_fill(0, count($search_array), "( performerName LIKE ?)"));    // Convert the array to a string for the SQL

// $option_type == "ssssss";
// $option_list == "SELECT field_1, field_2, field_3 FROM sections WHERE ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? )";

$sql   = "SELECT field_1, field_2, field_3 FROM sections WHERE {$option_list}";    // SQL statement merging with $option_list
$query = $mysqli->prepare($sql);                    // Prepare the query
$query->bind_param($var_type, ...$search_array);    // Bind parameters to query
$query->execute();                                  // Execute the query
$query->store_result();                             // Catch the result
$query->bind_result($field1, $field2, $field3);     // Bind the columns to variables

while($query->fetch()){
     // Do something with each row of returned data...

     echo "Name: {$field1}<br>";    // Example, echo name of performer (field_1)
}

PDO [1]

$performerName = "JOANNE GRACE SCHOOL OF DANCE";

$search_string = preg_replace("/(\w+)/i", "%$1%", $performerName);
$search_array  = explode(" ", $search_string);

$option_list = implode(" OR ", array_fill(0, count($search_array), "( name LIKE ? )"));

// $option_list == "SELECT field_1, field_2, field_3 FROM sections WHERE ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? )";

$sql   = "SELECT field_1, field_2, field_3 FROM sections WHERE {$option_list}";    // SQL statement merging with $option_list
$query = $pdo->prepare($sql);
$query->execute($search_array);
$query->bindColumn('name', $name);

while($query->fetch()){
     // Do something with each row of returned data...

     echo "Name: {$field1}<br>";    // Example, echo name of performer (field_1)
}

PDO [2]

$performerName = "JOANNE GRACE SCHOOL OF DANCE";

$search_string = preg_replace("/(\w+)/i", "%$1%", $performerName);
$search_array  = explode(" ", $search_string);

$option_list = implode(" OR ", array_fill(0, count($search_array), "( name LIKE ? )"));

// $option_list == "SELECT field_1, field_2, field_3 FROM sections WHERE ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? ) OR ( performerName LIKE ? )";

$sql   = "SELECT field_1, field_2, field_3 FROM sections WHERE {$option_list}";    // SQL statement merging with $option_list
$query = $pdo->prepare($sql);
$query->execute($search_array);

while($record = $query->fetchObject()){
     // Do something with each row of returned data...

     echo "Name: {$record->field_1}<br>";    // Example, echo name of performer (field_1)
}

当然，您可以使用REGEXP.

一些注意事项

field_name LIKE '%WORD%'将与WORD它周围的任何字符匹配。这意味着你可能会得到一些意想不到的结果......

Search String        Matches

%the%                "the", " the ", "other", "clothes"
%app%                "app", "apple", "schnapps"