如何结合“LIKE"带有“IN"在 MYSQL 查询中?

2022-01-06 00:00:00 sql-like php mysql joomla

我有这个代码在 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 服务器版本以使用正确的语法Near '%'apartment'%) GROUP BY p.id ORDER BY ap.price DESC LIMIT 0,20' 在第 12 行 SQL=SELECT p., p.id AS id, p.title AS 标题,p.street_num, p.street, p.description as description,ap.price, pr.name作为 priceName,usr.id 作为 advert_user_id,countries.title 作为country_name,states.title as state_name,date_format(p.created,'%Y-%m-%d') 作为 fcreated，c.title 作为 category_title，c.id 作为 category_idFROM b1yvu_koparent AS p LEFT JOIN b1yvu_koparent_advert_pricesAS ap ON ap.advertId = p.id AND ap.advertDateRangeGroupId = 0 ANDap.priceId = p.priceUnitId LEFT JOIN b1yvu_koparent_prices AS prON pr.id = p.priceUnitId LEFT JOIN b1yvu_koparent_advertmid AS pmON pm.advert_id = p.id LEFT JOIN b1yvu_koparent_usermid AS am ONam.advert_id = p.id LEFT JOIN b1yvu_koparent_users AS usr ONusr.id = am.user_id LEFT JOIN b1yvu_koparent_categories AS c ONc.id = pm.cat_id 左连接b1yvu_koparent_advert_specific_fields_values AS asfv ONasfv.advert_id = p.id LEFT JOIN b1yvu_koparent_countries AScountries ON countries.id = p.country LEFT JOINb1yvu_koparent_states AS states ON states.id = p.locstate WHEREp.published = 1 AND p.approved = 1 AND c.published = 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 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 (SELECTadcfvc.advert_id FROM b1yvu_koparent_advert_specific_fields_values ASadcfvc 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

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

感谢任何帮助或建议.

推荐答案

使用 RLIKE 而不是接近的 LIKE 是解决我的问题的最简单和直接的解决方案.

Using RLIKE instead of the approached LIKE was the most simple and direct solution for my question.

最终完整查询:-

$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 RLIKE ' . $db->Quote(JString::strtolower($sf_value)) . '
)';

这使得结果返回其名称包含搜索值的所有项目.

This made the results return all items that their name included the searched value.

相关文章