OK, i've been working with this for some time and I'm a little stuck.
好吧,我已经使用了一段时间了,我有点卡住了。
Maybe I'm going about it all wrong!
也许我一切都错了!
Basically I have a query for a search field. The general idea is to select result based on LIKE %% whilst still putting exact matches first.
基本上我有一个搜索字段的查询。一般的想法是选择基于LIKE %%的结果,同时仍然将完全匹配放在第一位。
So for example if you search 47 I want everything with 47 in either id, surname, or company_name to show, however the result with id number 47 should be on top and the same if I typed a surname.
因此,例如,如果你搜索47我想要显示id,surname或company_name中47的所有内容,但是如果我键入姓氏,则id号为47的结果应该在顶部并且相同。
Please see my code below, it may help to clarify my question.
请参阅下面的代码,这可能有助于澄清我的问题。
SELECT id,
IF(company_name IS NOT NULL AND company_name <> '', company_name, surname) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM clients
WHERE id LIKE '$search' OR surname LIKE '$search' OR company_name LIKE '$search'
union all
SELECT id,
IF(company_name IS NOT NULL AND company_name <> '', company_name, surname) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM clients
WHERE id LIKE '%$search%' AND id NOT LIKE '$search' OR surname LIKE '%$search%'
and SURNAME NOT LIKE '$search' OR company_name LIKE '%$search%'
and company_name NOT LIKE '$search'
LIMIT $start, $limit";
`
`
1 个解决方案
#1
3
Try this:
尝试这个:
(select exact match) union all with (select partial match omitting exact match)
(选择完全匹配)union all with(选择部分匹配省略完全匹配)
Example:
例:
(
SELECT
id,
IF( company_name IS NOT NULL AND company_name <> '', company_name, surname ) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM
clients
WHERE
id LIKE '$search' OR
surname LIKE '$search' OR
company_name LIKE '$search'
)
union all
(
SELECT
id,
IF( company_name IS NOT NULL AND company_name <> '', company_name, surname ) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM
clients
WHERE
( id LIKE '%$search%' AND id NOT LIKE '$search' ) OR
( surname LIKE '%$search%' AND SURNAME NOT LIKE '$search' ) OR
( company_name LIKE '%$search%' AND company_name NOT LIKE '$search' )
)
LIMIT $start, $limit;
#1
3
Try this:
尝试这个:
(select exact match) union all with (select partial match omitting exact match)
(选择完全匹配)union all with(选择部分匹配省略完全匹配)
Example:
例:
(
SELECT
id,
IF( company_name IS NOT NULL AND company_name <> '', company_name, surname ) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM
clients
WHERE
id LIKE '$search' OR
surname LIKE '$search' OR
company_name LIKE '$search'
)
union all
(
SELECT
id,
IF( company_name IS NOT NULL AND company_name <> '', company_name, surname ) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM
clients
WHERE
( id LIKE '%$search%' AND id NOT LIKE '$search' ) OR
( surname LIKE '%$search%' AND SURNAME NOT LIKE '$search' ) OR
( company_name LIKE '%$search%' AND company_name NOT LIKE '$search' )
)
LIMIT $start, $limit;