CODEWITHSUNDEEP
mysqlsearch
Gowri
Gowri

Reputation: 16835

get matched result with used keyword in mysql like query

I am doing LIKE search using OR condition with multiple keywords. I am wondering is that possible to get the search result and used keywords for that result.

SELECT * FROM `LOCATIONS` WHERE `location_name` LIKE '%United Kingdom%' OR `location_name` LIKE '%France%';

The query get's the matched results with both keywords, I want to get the match is from which keyword like this!

location_name                 usedKeyword

United Kingdom UK             United Kingdom

Lille, France                 France

London, United Kingdom        United Kingdom

Event it's possible I know there is one more problem if location_name matched for both keywords. I really don't have idea. How to do this.

for example, "United Kingdom and France" is location name

Why you want to do this ?

You may have this question, I experimenting all the possible ways to get the locations country name. I have countrylist table with code and names. Using that I like to get the location names

Few other experiments

  1. reverse phrase search in zend lucene
  2. Location search query issue

PS: I know, I can run the query separatly for each country. I doing the same for cities means thousands, To minimize the number of queries, I am looking for alternative.

Upvotes: 1

Views: 161

Answers (1)

Barmar
Barmar

Reputation: 780974

Use a CASE expression:

SELECT *,
       CASE WHEN location_name LIKE '%United Kingdom%'
            THEN 'United Kingdom'
            WHEN location_name LIKE '%France%'
            THEN 'France'
       END usedKeyword
FROM `LOCATIONS`
WHERE `location_name` LIKE '%United Kingdom%'
   OR `location_name` LIKE '%France%';

Also see the corrected WHERE clause. Your WHERE clause would return all rows.

Upvotes: 1

Related Questions