I am building a data driven website (using PHP and MySQL) for some farmers around the community (well, was - you will see in a second). They wanted to be able to list their products and have people search those products and their names come up along with a link to a page detailing all of their produce.
我正在为社区周围的一些农民建立一个数据驱动的网站(使用PHP和MySQL)(好吧,你会在一秒钟内看到)。他们希望能够列出他们的产品,并让人们搜索这些产品,他们的名字和一个链接到一个页面详细说明他们所有的产品。
While I knew it would be a long list, I thought, "Since every search into a mysql database is picky - including case sensitive - I'll just make a list in alphabetical order and people can choose from a dropdown box what they want to search for. No problem."
虽然我知道这将是一个很长的列表,但我想,“因为每次搜索到mysql数据库都很挑剔 - 包括区分大小写 - 我只是按字母顺序排列,人们可以从下拉框中选择他们想要的内容搜索。没问题。“
Well, now it's a problem. He has expanded the parameters of the site. He now wants to included hand made and home made products. Needless to say we went from a few dozen to hundreds of potential products and now a dropdown list is no longer feasible. But if I use a text field for visitors to search the site, unless they type it with no spelling errors and use the same case, they won't get accurate results from the search.
好吧,现在这是一个问题。他扩展了网站的参数。他现在想要包括手工制作和自制产品。毋庸置疑,我们从几十种到数百种潜在产品,现在下拉列表已不再可行。但是,如果我使用文本字段供访问者搜索网站,除非他们输入没有拼写错误并使用相同的情况,他们将无法从搜索中获得准确的结果。
Can anyone recommend another method? I am aware of the "LIKE" search, but it doesn't really solve my problem - especially since it could create false positives in the search. Any help would be appreciated, thanks!
有谁能推荐另一种方法?我知道“LIKE”搜索,但它并没有真正解决我的问题 - 特别是因为它可能会在搜索中产生误报。任何帮助将不胜感激,谢谢!
2 个解决方案
#1
0
Well, the question is somewhat vague, since you are talking about multiple search parameters.
嗯,这个问题有些模糊,因为你在谈论多个搜索参数。
It's more a design choice. For example, consider the following:
这更像是一种设计选择。例如,请考虑以下事项:
- For items such as "homemade" or "handmade", perhaps underneath the input field, you should have a checkbox where people can add additional flags to the search
- say, search by name "John Smith" and check off "handmade" and "homemade"
- The "handmade" and "homemade" fields in the database will always be the same (either on / off)
对于“自制”或“手工制作”等项目,可能在输入字段下方,您应该有一个复选框,人们可以在其中添加其他标记到搜索
比如说,搜索名称“约翰史密斯”,并检查“手工制作”和“自制”
数据库中的“手工”和“自制”字段将始终相同(开/关)
So, an sql query might be like this:
所以,一个SQL查询可能是这样的:
SELECT * FROM products WHERE farmername LIKE '%$search%' AND handmade = 'handmade'
Or, when inputting the data, if handmade is checked, insert an integer into the handmade, where 1 would mean handmade was checked, 0 handmade was not checked
或者,在输入数据时,如果选中手工制作,则在手工制作中插入一个整数,其中1表示手工制作,但未检查手工制作
so your query would then go AND handmade = 1 (or 0) for not handmade
所以你的查询然后去手工制作= 1(或0)不手工制作
These are just some ideas to get you going, but this is more a design decision than a database decision, how do you create your tables to use the flags
这些只是让你前进的一些想法,但这更像是一个设计决策,而不是数据库决策,你如何创建表来使用标志
#2
0
I would use two tables:
我会用两个表:
- one with all possible search terms
- one with synonyms of any terms that were applicable (EG "handmade" and "homemade" )
一个包含所有可能的搜索词
一个与任何适用的术语的同义词(EG“手工制作”和“自制”)
Use AJAX to search for values from the first table as characters are entered in a text box. Return a list of possible terms using:
在文本框中输入字符时,使用AJAX从第一个表中搜索值。使用以下命令返回可能的术语列表:
select term from search_table where term like '%<input string>%'
从search_table中选择术语,其中术语如'% <输入字符串> %'
Only start returning values when you have less than 10 hits or so. (IE don't populate when they enter 2 letters ). Then when a particular term is entered, search in the second table for synonyms and include those with the search. In the results page, indicate that you included the synonyms and maybe put an 'X' by each to opt to re-search with those excluded.
只有少于10次点击时才开始返回值。 (当IE输入2个字母时,IE不会填充)。然后,当输入特定术语时,在第二个表中搜索同义词并包括搜索结果。在结果页面中,指示您包含了同义词,并且可能每个都设置一个“X”以选择重新搜索排除的那些。
Note that 'like' is case-insensitive.
请注意,'like'不区分大小写。
#1
0
Well, the question is somewhat vague, since you are talking about multiple search parameters.
嗯,这个问题有些模糊,因为你在谈论多个搜索参数。
It's more a design choice. For example, consider the following:
这更像是一种设计选择。例如,请考虑以下事项:
- For items such as "homemade" or "handmade", perhaps underneath the input field, you should have a checkbox where people can add additional flags to the search
- say, search by name "John Smith" and check off "handmade" and "homemade"
- The "handmade" and "homemade" fields in the database will always be the same (either on / off)
对于“自制”或“手工制作”等项目,可能在输入字段下方,您应该有一个复选框,人们可以在其中添加其他标记到搜索
比如说,搜索名称“约翰史密斯”,并检查“手工制作”和“自制”
数据库中的“手工”和“自制”字段将始终相同(开/关)
So, an sql query might be like this:
所以,一个SQL查询可能是这样的:
SELECT * FROM products WHERE farmername LIKE '%$search%' AND handmade = 'handmade'
Or, when inputting the data, if handmade is checked, insert an integer into the handmade, where 1 would mean handmade was checked, 0 handmade was not checked
或者,在输入数据时,如果选中手工制作,则在手工制作中插入一个整数,其中1表示手工制作,但未检查手工制作
so your query would then go AND handmade = 1 (or 0) for not handmade
所以你的查询然后去手工制作= 1(或0)不手工制作
These are just some ideas to get you going, but this is more a design decision than a database decision, how do you create your tables to use the flags
这些只是让你前进的一些想法,但这更像是一个设计决策,而不是数据库决策,你如何创建表来使用标志
#2
0
I would use two tables:
我会用两个表:
- one with all possible search terms
- one with synonyms of any terms that were applicable (EG "handmade" and "homemade" )
一个包含所有可能的搜索词
一个与任何适用的术语的同义词(EG“手工制作”和“自制”)
Use AJAX to search for values from the first table as characters are entered in a text box. Return a list of possible terms using:
在文本框中输入字符时,使用AJAX从第一个表中搜索值。使用以下命令返回可能的术语列表:
select term from search_table where term like '%<input string>%'
从search_table中选择术语,其中术语如'% <输入字符串> %'
Only start returning values when you have less than 10 hits or so. (IE don't populate when they enter 2 letters ). Then when a particular term is entered, search in the second table for synonyms and include those with the search. In the results page, indicate that you included the synonyms and maybe put an 'X' by each to opt to re-search with those excluded.
只有少于10次点击时才开始返回值。 (当IE输入2个字母时,IE不会填充)。然后,当输入特定术语时,在第二个表中搜索同义词并包括搜索结果。在结果页面中,指示您包含了同义词,并且可能每个都设置一个“X”以选择重新搜索排除的那些。
Note that 'like' is case-insensitive.
请注意,'like'不区分大小写。