0

Hi All,

I am uisng Mysql DB and i have a task for suggesting Categories based on User keyword input.
I have written the query as below but i need to ignore white spaces while matching, how can we achieve it

SELECT search.*,
MATCH (name) AGAINST ('black' IN BOOLEAN MODE) AS name_match,
MATCH (keywords) AGAINST ('black' IN BOOLEAN MODE) AS keyword_match,
MATCH (description) AGAINST ('black' IN BOOLEAN MODE) AS description_match
FROM search
WHERE MATCH (name, keywords, description) AGAINST ('black' IN BOOLEAN MODE)
ORDER BY (name_match * 3  + keyword_match * 2  + description_match) DESC LIMIT 0,100;

I tried using replace() which were throwing error

SELECT search.*,
    MATCH (replace(`name`,' ','')) AGAINST ('black' IN BOOLEAN MODE) AS name_match,
    MATCH (replace(`keywords`,' ','')) AGAINST ('black' IN BOOLEAN MODE) AS keyword_match,
    MATCH (replace(`description`,' ','')) AGAINST ('black' IN BOOLEAN MODE) AS description_match
    FROM search
    WHERE MATCH (name, keywords, description) AGAINST ('black' IN BOOLEAN MODE)
    ORDER BY (name_match * 3  + keyword_match * 2  + description_match) DESC LIMIT 0,100;
3
Contributors
3
Replies
21
Views
4 Years
Discussion Span
Last Post by pritaeas
-1

where this this code to be used in which app.

Regards

Edited by pritaeas: Removed fake signature.

0

I want it in my site. I am using PHP, Mysql and Apache web server.
What i want is to match the values of columns (name,keyword and description) ignoring whitespace

Ex. if my keyword column has "Tooth bursh" and User Inputs keyword "toothbrush" i will not get results if i run the above query

0

The problem with your query is that MATCH() has column names as parameters, and not the values from those columns. That's why the REPLACE() is not allowed.

It is very difficult to determine, if the user inputs "toothbrush", that you should also look for "tooth" and "brush". The only way to do that is to build a dictionary with related/similar words to be used in a join.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.