How to combine "LIKE" with "IN" in a MYSQL query?

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

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%".

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.

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.

Trial#1

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

= adcfvc.field_value

With:

LIKE %adcfvc.field_value%

with no success :(

Trial#2

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!

Trial#3

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

But this returns an error:

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.

Solution

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

Final full query:-

$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.