I wanted to add a search feature on a website that would allow users to search the whole website.
我想在网站上添加一个搜索功能,允许用户搜索整个网站。
The site has around 20 tables and i wanted the search to search through all 20 tables.
该网站有大约20个表,我希望搜索搜索所有20个表。
Any one can point me into what sort of mysql queries I need to build?
任何人都可以指出我需要构建什么样的mysql查询?
5 个解决方案
#1
First of all, what about adding custom Google websearch to your site?
首先,如何在您的网站上添加自定义Google网络搜索?
The hard way: You should propably do a query for each of your tables and LIMIT (with LIKE on text columns or use full text indexing if your database software supports this) the result to X (e.g. ten) results. In your code, somehow rate these results and display the X best results.
困难的方法:您应该对每个表和LIMIT(对文本列使用LIKE,或者如果数据库软件支持使用全文索引)进行查询,结果为X(例如十)结果。在您的代码中,以某种方式评估这些结果并显示X最佳结果。
You could also try to use a UNION of multiple queries but then the resulting tuples all have to same structure (if I remember correctly).
您也可以尝试使用多个查询的UNION,但结果元组都必须具有相同的结构(如果我没记错的话)。
#2
Search engines. My Comp Sci degree thesis. First of all you have to ask yourself the question. What type of search do you want to offer the user. If the user will clearly know what they are looking for, for example a product based website then you should provide a search engine based on meta-data. For example users will be searching for a specific product, or product type. This is generally quite easy to provide.
搜索引擎。我的Comp Sci学位论文。首先,你必须问自己这个问题。您希望为用户提供什么类型的搜索。如果用户清楚地知道他们在寻找什么,例如基于产品的网站,那么您应该提供基于元数据的搜索引擎。例如,用户将搜索特定产品或产品类型。这通常很容易提供。
The next is your familiar web search engine such as Google. Google here targets a completely different market. The typical user doesn't know exactly what they are looking for. They just know that they are looking for something to do with Aeroplanes for example. Now Google has to try and figure out what is the result that is most likely to match that and be the most relevant.
接下来是您熟悉的网络搜索引擎,例如Google。谷歌在这里针对一个完全不同的市场典型的用户并不确切地知道他们在寻找什么。他们只是知道他们正在寻找与飞机有关的事情。现在谷歌必须尝试找出最有可能匹配的结果,并且最相关。
I know Google has an incredibly complex and optimised system but from memory if you want to go this way you need to create something called an inverted index file. Then you need to start thinking about a thesaurus because what if the user types in cat, then you should also provide results that contain the word feline. Also word trees, because the user typed in cat the cats result will also be relevant.
我知道Google有一个非常复杂和优化的系统但是从内存中如果你想这样,你需要创建一个称为倒排索引文件的东西。然后你需要开始考虑同义词库,因为如果用户键入cat,那么你还应该提供包含单词feline的结果。单词树,因为用户输入猫猫的结果也是相关的。
I am pretty sure that if you are providing a search engine for your website then it most likely be a metadata search engine in which case you can roll your own solution. If not and you are looking for the second type then why not use Google's services. They provide a custom search that will work within your own website.
我很确定如果您为您的网站提供搜索引擎,那么它很可能是元数据搜索引擎,在这种情况下您可以推出自己的解决方案。如果没有,你正在寻找第二种类型,那么为什么不使用谷歌的服务。它们提供可在您自己的网站中使用的自定义搜索。
#4
1.: Set a FULLTEXT index on the fields with the content and use the fulltext search mysql provides: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
1:在带有内容的字段上设置FULLTEXT索引并使用mysql提供的全文搜索:http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
or
2.: Have a look at the lucene search the Zend Framework provides: http://framework.zend.com/manual/en/zend.search.lucene.html
2:看看Zend Framework提供的lucene搜索:http://framework.zend.com/manual/en/zend.search.lucene.html
#5
have u tried looking at lucene? its one of the best search modules available today. i would strongly suggest you to give it a shot
你有没有试过看lucene?它是当今最好的搜索模块之一。我强烈建议你试一试
#1
First of all, what about adding custom Google websearch to your site?
首先,如何在您的网站上添加自定义Google网络搜索?
The hard way: You should propably do a query for each of your tables and LIMIT (with LIKE on text columns or use full text indexing if your database software supports this) the result to X (e.g. ten) results. In your code, somehow rate these results and display the X best results.
困难的方法:您应该对每个表和LIMIT(对文本列使用LIKE,或者如果数据库软件支持使用全文索引)进行查询,结果为X(例如十)结果。在您的代码中,以某种方式评估这些结果并显示X最佳结果。
You could also try to use a UNION of multiple queries but then the resulting tuples all have to same structure (if I remember correctly).
您也可以尝试使用多个查询的UNION,但结果元组都必须具有相同的结构(如果我没记错的话)。
#2
Search engines. My Comp Sci degree thesis. First of all you have to ask yourself the question. What type of search do you want to offer the user. If the user will clearly know what they are looking for, for example a product based website then you should provide a search engine based on meta-data. For example users will be searching for a specific product, or product type. This is generally quite easy to provide.
搜索引擎。我的Comp Sci学位论文。首先,你必须问自己这个问题。您希望为用户提供什么类型的搜索。如果用户清楚地知道他们在寻找什么,例如基于产品的网站,那么您应该提供基于元数据的搜索引擎。例如,用户将搜索特定产品或产品类型。这通常很容易提供。
The next is your familiar web search engine such as Google. Google here targets a completely different market. The typical user doesn't know exactly what they are looking for. They just know that they are looking for something to do with Aeroplanes for example. Now Google has to try and figure out what is the result that is most likely to match that and be the most relevant.
接下来是您熟悉的网络搜索引擎,例如Google。谷歌在这里针对一个完全不同的市场典型的用户并不确切地知道他们在寻找什么。他们只是知道他们正在寻找与飞机有关的事情。现在谷歌必须尝试找出最有可能匹配的结果,并且最相关。
I know Google has an incredibly complex and optimised system but from memory if you want to go this way you need to create something called an inverted index file. Then you need to start thinking about a thesaurus because what if the user types in cat, then you should also provide results that contain the word feline. Also word trees, because the user typed in cat the cats result will also be relevant.
我知道Google有一个非常复杂和优化的系统但是从内存中如果你想这样,你需要创建一个称为倒排索引文件的东西。然后你需要开始考虑同义词库,因为如果用户键入cat,那么你还应该提供包含单词feline的结果。单词树,因为用户输入猫猫的结果也是相关的。
I am pretty sure that if you are providing a search engine for your website then it most likely be a metadata search engine in which case you can roll your own solution. If not and you are looking for the second type then why not use Google's services. They provide a custom search that will work within your own website.
我很确定如果您为您的网站提供搜索引擎,那么它很可能是元数据搜索引擎,在这种情况下您可以推出自己的解决方案。如果没有,你正在寻找第二种类型,那么为什么不使用谷歌的服务。它们提供可在您自己的网站中使用的自定义搜索。
#3
#4
1.: Set a FULLTEXT index on the fields with the content and use the fulltext search mysql provides: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
1:在带有内容的字段上设置FULLTEXT索引并使用mysql提供的全文搜索:http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
or
2.: Have a look at the lucene search the Zend Framework provides: http://framework.zend.com/manual/en/zend.search.lucene.html
2:看看Zend Framework提供的lucene搜索:http://framework.zend.com/manual/en/zend.search.lucene.html
#5
have u tried looking at lucene? its one of the best search modules available today. i would strongly suggest you to give it a shot
你有没有试过看lucene?它是当今最好的搜索模块之一。我强烈建议你试一试