如何结合“喜欢"带有"IN"在MYSQL查询?

我有以下代码可在MYSQL数据库中搜索匹配结果:

I have this code to search for a matching result in a MYSQL database:

$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"运算符选择任何匹配结果.

I want to change the above search query from selecting exact match using the equal sign "=" operator to selecting any matching result using the "LIKE" operator with two wildcards "%adcfvc.field_value%".

换句话说:上面代码的当前作用是，当用户搜索"Hello my people"时，查询将搜索确切的单词. 但是，我希望用户只能使用单词"Hello"或"people"进行搜索，并且他会获得所有结果，包括"Hello my people".

In other words: the current role of the above code is that when the user search for "Hello my people" the query will search for the exact word. However, I want the user to be able to search using the word "Hello" or "people" only, and he get all results including "Hello my people".

知道我仍然无法更改任何数据库结构，只需修改上面的代码即可.

Knowing that I cannot change any of the database structure in anyway, just modify the above code.

可以在以下位置找到名为"query.php"的整个代码文件: http://123dizajn.com/boltours/stackex/query.txt 我无法在此处粘贴整个代码，因为它超出了正文限制，并且为了便于查看，将其重命名为query.txt.

The entire code file called "query.php" is available at: http://123dizajn.com/boltours/stackex/query.txt I couldn't paste the whole code here as it exceeded body limits, and it was renamed to query.txt just to be viewable.

所以，我试图替换(在代码的最后):

So, I tried to just replace (at the very end of the code):

= adcfvc.field_value

使用:

LIKE %adcfvc.field_value%

没有成功:(

我试图反转查找顺序并使用多个逻辑运算符:-

I tried to reverse the lookup order and use multiple logical operators:-

$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)) . ')
)';

但这会返回所有项目，而不是搜索到的项目！

But this returns all items, not the searched ones!

我也尝试过反转并使用LIKE %...%:-

I also tried to reverse and use the 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服务器版本以使用正确的语法 接近'％'apartment'％))GROUP BY p.id ORDER BY ap.price DESC LIMIT 0， 第12行20'SQL = SELECT p.，p.id AS id，p.title AS标题， p.street_num，p.street，p.description作为说明，价格，名称 作为priceName，usr.id作为advert_user_id，countries.title作为 country_name，states.title为state_name，date_format(p.created， 已创建'％Y-％m-％d')，c.title为category_title，c.id为category_id 从b1yvu_koparent作为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 pm.advert_id = p.id左联接b1yvu_koparent_usermid AS am开启 am.advert_id = p.id左联接b1yvu_koparent_users AS usr ON usr.id = am.user_id左联接b1yvu_koparent_categories AS c ON c.id = pm.cat_id左加入 b1yvu_koparent_advert_specific_fields_values AS asfv开启 asfv.advert_id = p.id左联接b1yvu_koparent_countries AS countries ON country.id = p.country左加入 b1yvu_koparent_states AS states ON States.id = p.locstate在哪里 p.已发布= 1 AND p.已批准= 1 AND c.已发布= 1 AND (p.publish_up ='0000-00-00'或p.publish_up< ='2015-09-05')并且 (p.publish_down ='0000-00-00'或p.publish_down> ='2015-09-05')并且 (c.publish_up ='0000-00-00'或c.publish_up< ='2015-09-05')并且 (c.publish_down ='0000-00-00'或c.publish_down> ='2015-09-05')并且 p.access输入(1,9)和c.access输入(1,9)和c.语言输入 ('en-GB'，'')和p.language IN('en-GB'，'*')和p.id IN(SELECT adcfvc.advert_id FROM b1yvu_koparent_advert_specific_fields_values AS adcfvc在哪里adcfvc.advert_id = p.id AND adcfvc.field_name = 't4_cust_AdvertTitleEN'和adcfvc.field_value类似于％'apartment'％) GROUP BY p.id ORDER BY ap.price DESC LIMIT 0，20

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'apartment'%) GROUP BY p.id ORDER BY ap.price DESC LIMIT 0, 20' at line 12 SQL=SELECT p., p.id AS id, p.title AS title, p.street_num, p.street, p.description as description,ap.price, pr.name as priceName,usr.id as advert_user_id,countries.title as country_name,states.title as state_name,date_format(p.created, '%Y-%m-%d') as fcreated,c.title as category_title, c.id as category_id FROM b1yvu_koparent AS p LEFT JOIN b1yvu_koparent_advert_prices AS ap ON ap.advertId = p.id AND ap.advertDateRangeGroupId = 0 AND ap.priceId = p.priceUnitId LEFT JOIN b1yvu_koparent_prices AS pr ON pr.id = p.priceUnitId LEFT JOIN b1yvu_koparent_advertmid AS pm ON pm.advert_id = p.id LEFT JOIN b1yvu_koparent_usermid AS am ON am.advert_id = p.id LEFT JOIN b1yvu_koparent_users AS usr ON usr.id = am.user_id LEFT JOIN b1yvu_koparent_categories AS c ON c.id = pm.cat_id LEFT JOIN b1yvu_koparent_advert_specific_fields_values AS asfv ON asfv.advert_id = p.id LEFT JOIN b1yvu_koparent_countries AS countries ON countries.id = p.country LEFT JOIN b1yvu_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' OR p.publish_up <= '2015-09-05') AND (p.publish_down = '0000-00-00' OR p.publish_down >= '2015-09-05') AND (c.publish_up = '0000-00-00' OR c.publish_up <= '2015-09-05') AND (c.publish_down = '0000-00-00' OR c.publish_down >= '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 FROM b1yvu_koparent_advert_specific_fields_values AS adcfvc WHERE adcfvc.advert_id = p.id AND adcfvc.field_name = 't4_cust_AdvertTitleEN' AND adcfvc.field_value LIKE %'apartment'%) GROUP BY p.id ORDER BY ap.price DESC LIMIT 0, 20

任何帮助或建议表示赞赏.

Any help or suggestions appreciated.