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;

Recommended Answers

All 3 Replies

where this this code to be used in which app.

Regards

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.