I currently have a search that looks like this:
我目前的搜索结果如下:
SELECT s.ID
FROM Shoe s
INNER JOIN Account a
ON s.UserID = a.ID
WHERE s.Publish='1' AND
(s.Brand LIKE '%$Search%'
OR s.Name LIKE '%$Search%'
OR s.PrimaryColor LIKE '%$Search%'
OR a.User LIKE '%$Search%')
ORDER BY s.ID DESC
LIMIT $offset, $rowsPerPage"
This works fine when I do a search such as "Blue" or "Nikes", but if I do a search such as "Blue Nikes" nothing returns. Should I use FULLTEXT
? How can I improve this? I want to be able to search all columns that may relate to the search variable.
当我进行诸如“蓝色”或“耐克”之类的搜索时,这样可以正常工作,但如果我进行诸如“蓝色耐克”之类的搜索,则无法返回。我应该使用FULLTEXT吗?我怎样才能改善这个?我希望能够搜索可能与搜索变量相关的所有列。
2 个解决方案
#1
1
So after messing around and testing different things, I came up with this:
所以在搞乱并测试不同的东西之后,我想出了这个:
"FROM Shoe AS s
LEFT JOIN Accounts AS a ON s.UserID = a.ID
WHERE (MATCH (s.Brand, s.Name, s.PrimaryColor AGAINST('$Search' IN BOOLEAN MODE)
OR MATCH (a.User) AGAINST('$Search' IN BOOLEAN MODE))
AND s.Publish='1'
ORDER BY s.ID DESC"
This seems to fix my issue that I mentioned above, I can now do a search such as "Blue Nike" and all items related to blue & nike will show up. Not sure if this is the most efficient way to go about it, but it does work.
这似乎解决了我上面提到的问题,我现在可以进行搜索,例如“Blue Nike”,所有与blue&nike相关的项目都会显示出来。不确定这是否是最有效的方法,但确实有效。
#2
0
Instead of LIKE
try using SOUNDEX
, or both.
而不是LIKE尝试使用SOUNDEX,或两者兼而有之。
Secondly, you may want to optimize the query to maintain capitalization, for example:
其次,您可能希望优化查询以维护大小写,例如:
(lower(s.Brand) LIKE '%" . strtolower($Search) . "%'
OR lower(s.Name) LIKE '%" . strtolower($Search) . "%'
OR lower(s.PrimaryColor) LIKE '%" . strtolower($Search) . "%'
OR lower(a.User) LIKE '%" . strtolower($Search) . "%')
#1
1
So after messing around and testing different things, I came up with this:
所以在搞乱并测试不同的东西之后,我想出了这个:
"FROM Shoe AS s
LEFT JOIN Accounts AS a ON s.UserID = a.ID
WHERE (MATCH (s.Brand, s.Name, s.PrimaryColor AGAINST('$Search' IN BOOLEAN MODE)
OR MATCH (a.User) AGAINST('$Search' IN BOOLEAN MODE))
AND s.Publish='1'
ORDER BY s.ID DESC"
This seems to fix my issue that I mentioned above, I can now do a search such as "Blue Nike" and all items related to blue & nike will show up. Not sure if this is the most efficient way to go about it, but it does work.
这似乎解决了我上面提到的问题,我现在可以进行搜索,例如“Blue Nike”,所有与blue&nike相关的项目都会显示出来。不确定这是否是最有效的方法,但确实有效。
#2
0
Instead of LIKE
try using SOUNDEX
, or both.
而不是LIKE尝试使用SOUNDEX,或两者兼而有之。
Secondly, you may want to optimize the query to maintain capitalization, for example:
其次,您可能希望优化查询以维护大小写,例如:
(lower(s.Brand) LIKE '%" . strtolower($Search) . "%'
OR lower(s.Name) LIKE '%" . strtolower($Search) . "%'
OR lower(s.PrimaryColor) LIKE '%" . strtolower($Search) . "%'
OR lower(a.User) LIKE '%" . strtolower($Search) . "%')