我有以下代码来在 MYSQL 数据库中搜索匹配结果:
$where[] = 'p.id IN (
SELECT adcfvc.advert_id
FROM #__koparent_advert_specific_fields_values AS adcfvc
WHERE adcfvc.advert_id = p.id
AND adcfvc.field_name = ' . $db->Quote($sf_key) . '
AND ' . $db->Quote(JString::strtolower($sf_value)) . ' = adcfvc.field_value
)';
我想将上述搜索查询从使用等号“=”运算符选择完全匹配更改为使用带有两个通配符“%adcfvc.field_value%”的“LIKE”运算符选择任何匹配结果。
换句话说:上述代码的当前作用是,当用户搜索“Hello my people”时,查询将搜索确切的单词。
但是,我希望用户能够仅使用“Hello”或“people”一词进行搜索,并且他会得到包括“Hello my people”在内的所有结果。
知道我无论如何都无法更改任何数据库结构,只需修改上面的代码即可。
名为“query.php”的整个代码文件可在以下位置找到:http://123dizajn.com/boltours/stackex/query.txt http://123dizajn.com/boltours/stackex/query.txt我无法将整个代码粘贴到此处,因为它超出了正文限制,并且将其重命名为 query.txt 只是为了便于查看。
Trial#1
所以,我尝试替换(在代码的最后):
= adcfvc.field_value
With:
LIKE %adcfvc.field_value%
没有成功:(
Trial#2
我尝试反转查找顺序并使用多个逻辑运算符:-
$where[] = 'p.id IN (
SELECT adcfvc.advert_id
FROM #__koparent_advert_specific_fields_values AS adcfvc
WHERE adcfvc.advert_id = p.id
AND adcfvc.field_name = ' . $db->Quote($sf_key) . '
AND
(adcfvc.field_value > ' . $db->Quote(JString::strtolower($sf_value)) . '
OR adcfvc.field_value < ' . $db->Quote(JString::strtolower($sf_value)) . '
OR adcfvc.field_value = ' . $db->Quote(JString::strtolower($sf_value)) . ')
)';
但这会返回所有项目,而不是搜索到的项目!
Trial#3
我也尝试扭转并使用LIKE %...%
:-
$where[] = 'p.id IN (
SELECT adcfvc.advert_id
FROM #__koparent_advert_specific_fields_values AS adcfvc
WHERE adcfvc.advert_id = p.id
AND adcfvc.field_name = ' . $db->Quote($sf_key) . '
AND adcfvc.field_value LIKE %' . $db->Quote(JString::strtolower($sf_value)) . '%
)';
但这会返回一个错误:
1064 您的 SQL 语法有错误;检查手册
与您的 MySQL 服务器版本相对应,以便使用正确的语法
靠近 '%'公寓'%) GROUP BY p.id ORDER BYap
.price
降序限制 0,
20' 在第 12 行 SQL=SELECT p., p.id AS id, p.title AS 标题,
p.street_num、p.street、p.description 作为描述、ap.price、pr.name
作为priceName,usr.id作为advert_user_id,countries.title作为
国家名称,states.title as 国家名称,日期格式(p.created,
'%Y-%m-%d') 作为 fcreated,c.title 作为类别_标题,c.id 作为类别_id
从b1yvu_koparent
AS p
左连接b1yvu_koparent_advert_prices
AS ap
ON ap.advertId = p.id AND ap.advertDateRangeGroupId = 0 AND
ap.priceId = p.priceUnitId 左连接b1yvu_koparent_prices
AS pr
ON pr.id = p.priceUnitId 左连接b1yvu_koparent_advertmid
AS pm
ON pm.advert_id = p.id 左连接b1yvu_koparent_usermid
AS am
在
am.advert_id = p.id 左连接b1yvu_koparent_users
AS usr
在
usr.id = am.user_id 左连接b1yvu_koparent_categories
AS c
在
c.id = pm.cat_id 左连接b1yvu_koparent_advert_specific_fields_values
AS asfv
在
asfv.advert_id = p.id 左连接b1yvu_koparent_countries
AS
countries
ON states.id = p.country LEFT JOINb1yvu_koparent_states
AS states
ON states.id = p.locstate WHERE
p.published = 1 AND p.approved = 1 AND c.published = 1 AND
(p.publish_up = '0000-00-00' 或 p.publish_up = '2015-09-05') AND
(c.publish_up = '0000-00-00' 或 c.publish_up = '2015-09-05') AND
p.access IN (1,9) AND c.access IN (1,9) AND c.language IN
('en-GB','') AND p.language IN ('en-GB','*') AND p.id IN (SELECT
adcfvc.advert_id 来自 b1yvu_koparent_advert_specific_fields_values AS
adcfvc 其中 adcfvc.advert_id = p.id AND adcfvc.field_name =
't4_cust_AdvertTitleEN' 和 adcfvc.field_value LIKE %'apartment'%)
按 p.id 分组 排序依据ap
.price
降序限制 0, 20
任何帮助或建议表示赞赏。