使用 LIKE % 进行任意词搜索 [英] any-word search using LIKE %

问题描述

我有一个数据库表，它有一个字段 firstname 和一个字段 lastname.我正在尝试使用搜索框实现一个任意词搜索系统，假设我们在数据库中有一个名为 John Stanley Smith 的用户(其中​​ firstname = John Stanleycode> 和 lastname = Smith)，如果用户键入以下任何内容，我希望能够返回 John Stanley Smith:

I have a database table which has a field firstname and a field lastname. I'm trying to implement an any-word search system using a search box where if let's say that we have a user named John Stanley Smith in the database (where firstname = John Stanley and lastname = Smith), I want to be able to return John Stanley Smith if the user types any of the following:

1- 属于 John Stanley Smith 的任何字母(j、s、l、e、y、m 等...)
2- 属于 John Stanley Smith
一部分的任何单词3- 属于 John Stanley Smith 一部分的任何单词组合(以任何顺序)，这意味着，如果用户输入 John Stanley 或 John Smith 或 Stanley Smith 或 Stanley John(你懂的)等等...我希望能够找到 John Stanley Smith.

1- Any letter which is part of John Stanley Smith (j, s, l, e, y, m, etc...)
2- Any word which is part of John Stanley Smith
3- Any combination of words (in any order) which is part of John Stanley Smith, meaning that, if the user enters either John Stanley or John Smith or Stanley Smith or Stanley John (you get the idea) etc... I want to be able to find John Stanley Smith.

我知道我可以使用 MySQL 全文搜索 功能，但如果我这样做，第一点将不起作用(因为 MySQL 全文搜索功能仅匹配整个单词，而不匹配字符串中的子字符串).此外，执行搜索所需的最少单词数(由 min_word_len 常量设置).所以，我真的很想使用 LIKE % 来做到这一点.

I know I can do this using the MySQL Fulltext Search feature, but if I do, point number one will not work (because the MySQL fulltext search feature only matches whole words, not substrings within a string). Also, there is a minimum number of words required for the search to be performed (set by the min_word_len constant). So, I really want to do this using LIKE %.

谢谢

推荐答案

希望对您有所帮助

$whereclauses=array();

$terms = explode(' ', $search_term);
foreach ($terms as $term) {
    $term = mysql_real_escape_string($term);
    $whereclauses[] = "CONCAT(first_name, ' ', last_name) LIKE '%$term%'";
}
$sql = "select * from table where";
$sql .= implode(' and ', $whereclauses);

这篇关于使用 LIKE % 进行任意词搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！