2

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

2

You should try FullText index and match...against queries.

As suggested by jkavalik:

You can do ORDER BY MATCH (id,keywords) AGAINST ('$search' IN NATURAL LANGUAGE MODE) desc to get the most relevant on top.

But what you should do is: create another table keywords with a Foreign key to the original table and store each keyword on a separate row, not in a comma separated list. Such lists will only give you headaches later.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.