How to search with keywords? (PHP)

Question

I was wondering how to generate a search SQL query for keywords:

I know:

SELECT * FROM table WHERE keyword LIKE %keyword%;

But how to work with multiple keywords.

I hope that makes sense...

try `WHERE keyword LIKE %keyword% or keyword LIKE %keyword2% or ..` change or to and if you want to match all keywords — M Khalid Junaid, Sep 03 '14 at 17:03
This may help http://stackoverflow.com/questions/1387612/how-can-i-introduce-multiple-conditions-in-like-operator — DavidT, Sep 03 '14 at 17:04
This may help too http://anthonykinson.co.uk/programing-internet/568_making-mysql-searches-relevant — Jonan, Sep 03 '14 at 17:05
Btw your query will fail. You have to wrap %keyword% in quotes: ... LIKE "%keyword%" — bksi, Sep 03 '14 at 17:06
There are different algorithms for search. You can use full text search integrated in mysql and sort by relevance explained here (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) or simple search you explain in your question — bksi, Sep 03 '14 at 17:08
Searching with wildcards on both sides can become quite inefficient when dealing with large data sets, even if the field is indexed. You may want to look into [FULLTEXT](http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html) searching in MySQL. In version 5.5 and below you can only use them with MyISAM tables, in 5.6+ you can use them in InnoDB tables as well. — Crackertastic, Sep 03 '14 at 17:09

score 1 · Answer 1 · answered Sep 03 '14 at 17:04

1

SELECT * 
FROM `table`
WHERE keyword LIKE '%1%'
OR keyword LIKE '%2%'
OR keyword LIKE '%3%'

If I understand you right, this is what you're looking for

answered Sep 03 '14 at 17:04

Alex Szabo

score 1 · Accepted Answer · answered Sep 03 '14 at 17:08

Please check with below codes:

You can use FULL TEXT SEARCH

SELECT col1 FROM table_name
WHERE MATCH (col1, col2, col3)
AGAINST ('keywords');

For this, you need to apply fulltext index on columns in which you want to search.

You can use IN operator too like -

SELECT col1 FROM table_name
WHERE col1 IN (keyword1, keyword2, keyword3, ...);

score 0 · Answer 3 · answered Sep 03 '14 at 17:05

You can use a combination of percent signs and underscore wildcards

SELECT * FROM table WHERE keyword LIKE "%keywordA_keywordB%";

Or use CONTAINS and do something like:

SELECT * FROM table 
    WHERE keyword CONTAINS "%keywordA%" AND keyword CONTAINS "%keywordB%";