MySQL search comma separated keywords

MySQLPHP

I have keywords column in my table and there are comma (,) separated values. example: hello, world

I have search engine with same codes:

$search = $_GET ['search'];

$query = $db->prepare("SELECT COUNT(*) as num FROM table WHERE keywords LIKE ?");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();

$sql = $db->prepare("SELECT * FROM table WHERE keywords LIKE ? LIMIT $start , $limit");
$sql->bindValue(1, "%$search%", PDO::PARAM_STR);
$sql->execute();

When I type only 'hello' or only 'world' in my search bar, there is result, but if I type both words together, like 'Hello world' there is no result.

EDIT:
I edited my codes for FULL TEXT SEARCH

$query = $db->prepare("SELECT COUNT(*) as num FROM table WHERE MATCH (id,keywords) AGAINST ('$search*' IN BOOLEAN MODE)");
$query->execute();

and

$sql = $db->prepare("SELECT * FROM table WHERE MATCH (id,keywords) AGAINST ('$search*' IN BOOLEAN MODE) LIMIT $start , $limit");
$sql->execute();

It works better, but now I get some irrelevant results. Is there any way to filter results and order they relevance?
Thanks

Best Answer

If you must keep keywords as a CSV, you can use FIND_IN_SET().

SELECT COUNT(*) as num 
FROM table 
WHERE FIND_IN_SET('hello', keywords) 
AND FIND_IN_SET('world', keywords);

Related Solutions

Mysql – Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

Perhaps you could record the following from the three(3) tables

table name
the column from the table name
FULLTEXT index on the column

Here is the code:

DROP TABLE IF EXISTS combined_data;
CREATE TABLE combined_data
(
    source_table VARCHAR(64),
    source_id INT NOT NULL,
    data TEXT NOT NULL,
    FULLTEXT (data)
) ENGINE=MyISAM;
--
ALTER TABLE combined_data DISABLE KEYS;
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table1',id,`result` FROM table1 WHERE MATCH(`result`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table2',id,`content` FROM table1 WHERE MATCH(`content`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table3',id,`text` FROM table1 WHERE MATCH(`text`) AGAINST('keyword');
--
ALTER TABLE combined_data ENABLE KEYS;

Now you can run a single query against the one table

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score`
FROM combined_data
ORDER BY `good_score` DESC;

Give it a Try !!!

MySQL Fulltext match with forward slashes

I suspect (with a high degree of of confidence) that the / isn't considered a "word" character. If that is the case, then Fine Tuning MySQL Full Text Search in the manual explains how to accomplish what you're wanting to do.

If you want to change the set of characters that are considered word characters, you can do so in several ways, as described in the following list. After making the modification, you must rebuild the indexes for each table that contains any FULLTEXT indexes. Suppose that you want to treat the hyphen character ('-') as a word character. Use one of these methods...

The second option offered there is probably the simplest, since you can apparently just edit the Character Definition Array in the appropriate XML file -- assuming you can use a one-byte character set such as latin1 for this column of this table. If you need utf8 or anything else multibyte, it apparently means recompiling the server from source after customizing the source to your specific needs.

Related Question