PHP MySQL 按相关性搜索和排序

2021-12-20 00:00:00 search php mysql

我知道如何进行常规的 php mysql 搜索并显示结果.但是,由于我要完成的工作的性质,我需要能够按相关性进行排序.让我更好地解释一下:

I know how to do a regular php mysql search and display the results. However, because of the nature of what I'm trying to accomplish I need to be able to sort by relevancy. Let me explain this better:

普通查询apple iphone applications"将使用 %apple iphone application% 搜索数据库,但如果没有以该确切顺序显示该短语的记录,则搜索将不会产生任何结果.

Normal Query "apple iphone applications" will search the database using %apple iphone application%, but if there aren't records which display that phrase in that exact order the search will produce nothing.

我基本上需要做的是分别搜索 'apple'、'iphone' 和 'applications',然后将结果合并为一个,然后我需要根据找到的单词实例的数量来对相关性进行评分在记录中.例如,如果我做了我想做的事情并且它返回了以下内容:

What I basically need to do is search for 'apple', 'iphone' and 'applications' all separately and then merge the results into one, and then I need to grade the relevancy by how many instances of the word are found in the records. For example if I did what I wanted to do and it returned them following:

Iphone Applications From Apple
Apple Make The Best Apple Iphone Applications
Iphone Applications

他们的排名如下:

Apple Make The Best Apple Iphone Applications
Iphone Applications From Apple
Iphone Applications

因为找到了多少个搜索词实例.请参阅突出显示:

Because of how many instances of the search terms are found. See highlighted:

[Apple] Make The Best [Apple] [Iphone] [Applications]
[Iphone] [Applications] From [Apple]
[Iphone] [Applications]

我希望我已经解释得足够好,如果有人能给我任何指点,我将不胜感激.

I hope I have explained this well enough and I would be extremely grateful if anyone could give me any pointers.

推荐答案

查看MySQL FULLTEXT 搜索功能,这些应该会自动按相关性返回结果,并让您更好地控制您的搜索

take a look at the MySQL FULLTEXT search functions, These should automatically return results by relevancy, and give you much more control over your searches

使用全文索引的唯一潜在问题是 InnoDB 表不支持它们.

The only potential issue with using fulltext indexes is that they aren't supported by InnoDB tables.

相关文章