在我的网站上,用户输入多个搜索字词。 这些搜索词中的每一个都可以返回0行,或者返回几行。 我在所有搜索上执行UNION并获取行。
如果我可以在响应中获得所有搜索项,那么对我来说更容易,无论它们是否返回任何行。 有没有办法基本上LEFT JOIN一个字符串(在这种情况下是搜索项)到SELECT查询?
更多信息更新:
我有一个表与字段标题的书。
用户最多输入10个标题search_terms,我目前在所有这些上执行UNION以获得匹配的行。 就这样
SELECT $s[0] AS Search_Term,* WHERE Title LIKE '%$s[0]%' UNION SELECT $s[1] AS Search_Term,* WHERE Title LIKE '%$s[1]%' ...etc我希望如果没有针对给定搜索的结果,我仍然会将Search_Term与NULL标题一起返回。
On part of my site, users enter multiple search terms. Each of these search terms may return 0 rows, or return several. I perform a UNION on all the searches and get the rows.
Thing are much easier for me if I can get all the search terms in the response, regardless of whether they return any rows. Is there a way to essentially LEFT JOIN a string (in this case the search term) to a SELECT query?
More information update:
I have a table Books with the field Title.
The user enters up to 10 title search_terms, and I currently perform a UNION on all these to get matching rows. So it's like
SELECT $s[0] AS Search_Term,* WHERE Title LIKE '%$s[0]%' UNION SELECT $s[1] AS Search_Term,* WHERE Title LIKE '%$s[1]%' ...etcI'd like it so that if there are no results for a given search, I still get the Search_Term back along with the NULL Title.
最满意答案
如果将数组值连接在一起,可以使用这种方式使用全文搜索(FTS):
SELECT x.searchterm, b.* FROM (SELECT $s[0 ... 20] AS search_term) x LEFT JOIN BOOKS b ON MATCHES(b.title) AGAINST (x.searchterm)$s[0 ... 20]是一个占位符,用于连接成单个字符串的搜索词,用空格字符分隔每个词。
如果没有匹配项,LEFT JOIN将确保您获得搜索项和其余列的NULL。
You could use Full Text Search (FTS) this way, if you concatenate the array values together:
SELECT x.searchterm, b.* FROM (SELECT $s[0 ... 20] AS search_term) x LEFT JOIN BOOKS b ON MATCHES(b.title) AGAINST (x.searchterm)$s[0 ... 20] is a placeholder for the search terms concatenated into a single string, separating each term with a space character.
The LEFT JOIN will ensure that you get your search term and NULL for the rest of the columns if nothing matches.
更多推荐
发布评论