CODEWITHSUNDEEP
mysqlsql
H. Wang
H. Wang

Reputation: 138

How to match keywords with SQL query?

I am building a website that accepts user input keywords and output data that matches. For instance if the user specifies keyword as 'Restaurant pizza' then my database should output such record.

My current table has a column called category and five columns named from keyword1 to keyword5 which contains their specialized area, i.e. 'pizza', 'chicken' or 'bbq' etc.

But I have no idea how to write the SQL query since user may input keywords in any order: category first or with specialized area first.

so such query will surely return no result (given that user input 'Restaurant pizza' for query):

SELECT * 
   FROM message 
 WHERE  category LIKE 'Restaurant pizza' 
 OR     keyword1 LIKE 'Restaurant pizza' 
 OR     keyword2 LIKE 'Restaurant pizza'

I guess it would be a bad idea splitting input keyword into words then running every word in the WHERE clause to database. but I really do not know how to achieve my goal.

In addition, would you please give me some advice on how to build index in this scenario?

Upvotes: 2

Views: 3235

Answers (2)

Bogdan Kuštan
Bogdan Kuštan

Reputation: 5577

You should create FULLTEXT index on category and keywords columns, then when querying data explode query string by delimeters (space character) and then create query something like:

SELECT * FROM items 
  WHERE 
  MATCH (category,keyword1,keyword2,keyword3,keyword4,keyword5) 
  AGAINST ('pizza') 
  AND 
  MATCH (category,keyword1,keyword2,keyword3,keyword4,keyword5) 
  AGAINST ('restaurant');

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

You may try this:

SELECT *  
FROM message  
WHERE `category` LIKE '%Restaurant%'  
AND (`keyword1` IN ('Pizza','chicken','bbq')  
OR `keyword2` IN ('Pizza','chicken','bbq'))

Upvotes: 1

Related Questions