CODEWITHSUNDEEP
mysql
Ben
Ben

Reputation: 11188

MySQL search query, sort result by best matches

I'm working on a searchengine within a website where a visitor can provide some keywords and then the searchengine performs some queries over a datatable.

I know how to query a table, but how can you build a single query which provides a full resultlist and per matching row in the datatable the number of occurences.

So say, i have simple table like:

id | title | desc
-----------------
1  | title 1 | some text with some text provding the user most likely no interesting info.

2 | title 2 | some other text

3 | title 3 | some third text saying some other crap.

What i'm trying to achieve is: when a user searches for 'some text' i get a result like:

id | title | desc | noOfMatches
-------------------------------
1  | title 1 | ... | 4

2  | title 3 | ... | 3

3  | title 2 | ... | 2

Can someone help me with this one? I'm not sure how to create a query that can count the no. of occorences of a provided word in a query...

Upvotes: 0

Views: 3346

Answers (1)

Quassnoi
Quassnoi

Reputation: 425351

Use FULLTEXT search:

CREATE TABLE t_ft (id INT NOT NULL PRIMARY KEY, words VARCHAR(255) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET='utf8';

CREATE FULLTEXT INDEX fx_ft_words ON t_ft (words);
INSERT
INTO    t_ft (id, words)
VALUES  (1, 'some text with some text provding the user most likely no interesting info');

INSERT
INTO    t_ft (id, words)
VALUES  (2, 'some other text');

INSERT
INTO    t_ft (id, words)
VALUES  (3, 'some third text saying some other crap');

INSERT
INTO    t_ft
SELECT  id + 3, 'complete nonsense'
FROM    t_source
LIMIT   1000;

SELECT  *, MATCH(words) AGAINST('"some text"') AS relevance
FROM    t_ft
WHERE   MATCH(words) AGAINST('"some text"')
ORDER BY
        relevance DESC;

MATCH will return you the rank which you can use in ORDER BY.

Upvotes: 2

Related Questions