使用php和mysql数据库时如何实现有效的搜索算法? [英] how to implement the an effective search algorithm when using php and a mysql database?

问题描述

我是Web设计(尤其是后端设计)的新手，所以我对在PHP中实现搜索功能有一些疑问.我已经建立了一个MySQL连接，但是我不知道如何访问MySQL表中的特定行.考虑到我想返回与搜索词几乎相同的结果，是否也正确实现了类似的文本函数?现在，我只能返回完全相同的结果，否则将返回无结果".例如，如果我搜索"tex"，它将返回包含"text"的结果?我意识到我的编码和逻辑中有很多错误，因此请尽可能提供帮助.事件是我要访问的行的名称.

I'm new to web design, especially backend design so I have a few questions about implementing a search function in PHP. I already set up a MySQL connection but I don't know how to access specific rows in the MySQL table. Also is the similar text function implemented correctly considering I want to return results that are nearly the same as the search term? Right now, I can only return results that are the exact same or it gives "no result." For example, if I search "tex" it would return results containing "text"? I realize that there are a lot of mistakes in my coding and logic, so please help if possible. Event is the name of the row I am trying to access.

$input = $_POST["searchevent"];
while ($events = mysql_fetch_row($Event)) {
$eventname = $events[1];
$eventid = $events[0];
$diff = similar_text($input, $event, $hold)
if ($hold == '100') {
echo $eventname;

break;

else
echo "no result";

}

谢谢.

我注意到一些评论中提到的执行搜索的方法比使用相似文本"功能更有效，如果我要使用LIKE函数，它将如何实现?

I've noticed some of the comments mentioned more efficient ways of performing the search than with the "similar text" function, if I were to use the LIKE function, how would it be implemented?

推荐答案

几种不同的方法:

更快(在性能方面)是: select * FROM Table where keyword LIKE '%value%' 这个技巧是将％放置为通配符，它​​表示搜索以该值结尾或以该值开头的所有内容.

The faster one (performance wise) is: select * FROM Table where keyword LIKE '%value%' The trick in this one is the placement of the % which is a wildcard, saying either search everything that ends or begins with this value.

一种更灵活但稍慢的功能可能是REGEXP函数:

A more flexible but (slightly) slower one could be the REGEXP function:

Select * FROM Table WHERE keyword REGEXP 'value'

这使用了正则表达式的功能，因此您可以对它进行详尽的设计.但是，如上所示，将为您提供一个穷人的Google"，使搜索成为零零碎碎的整个工作领域.

This is using the power of regular expressions, so you could get as elaborate as you wanted with it. However, leaving as above gives you a "poor man's Google" of sorts, allowing the search to be bits and pieces of overall fields.

如果您要搜索名称，则会出现粘性部分.例如，如果您搜索SMI，则两者都将找到名称"smith".但是，如果名字和姓氏字段分开，则找不到"Jon Smith".因此，您必须进行一些级联搜索才能找到Jon或Smith或Jon Smith或Smith，Jon.它真的可以从那里滚雪球.

The sticky part comes in if you're trying to search names. For example, either would find the name "smith" if you searched SMI. However, neither would find "Jon Smith" if there was a first and last name field separated. So, you'd have to do some concatenation for the search to find either Jon OR Smith OR Jon Smith OR Smith, Jon. It can really snowball from there.

当然，如果您要进行某种高级搜索，则必须相应地设置查询条件.因此，例如，如果您想搜索第一个，最后一个地址，那么您的查询将必须针对每个地址进行测试:

Of course, if you're doing some sort of advanced search, you'll have to condition your query accordingly. So, for instance, if you wanted to search first, last, address, then your query would have to test for each:

SELECT * FROM table WHERE first LIKE '%value%' OR last LIKE '%value%' OR address LIKE '%value'

这篇关于使用php和mysql数据库时如何实现有效的搜索算法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！