PHP + MYSQL multi-keyword + fuzzy search + matching sorting. how can this problem be achieved?

PHP + MYSQL multi-keyword + fuzzy search + matching sorting. how can this problem be achieved? Mysql php fuzzy search sorting

Mysql union has been used to implement one, but the efficiency is too low. please ask the experts on the forum if there is a better way.

Reply to discussion (solution)

Paste your table structure and SQL

This post was last edited by xuzuning at 10:18:55

SQL:

(SELECT. 'askid',. 'catid',. 'title',. 'status',. 'addtime',. 'reward ',. 'ancercount', B. 'message' FROM 'phpcms _ ask 'a left join 'phpcms _ ask_posts' B ON. 'askid' = B. 'askid' WHERE (. status = 3 OR. status = 5) AND (LOCATE ('Tiananmen, Beijing, China',. 'title')> 0) AND B. 'isask '= 1) UNION (SELECT. 'askid',. 'catid',. 'title',. 'status',. 'addtime',. 'reward ',. 'ancercount', B. 'message' FROM 'phpcms _ ask 'a left join 'phpcms _ ask_posts' B ON. 'askid' = B. 'askid' WHERE (. status = 3 OR. status = 5) AND (LOCATE ('China',. 'title')> 0 and locate ('Beijing',. 'title')> 0 and locate ('Tiananmen Square ',. 'title')> 0) AND B. 'isask '= 1) UNION (SELECT. 'askid',. 'catid',. 'title',. 'status',. 'addtime',. 'reward ',. 'ancercount', B. 'message' FROM 'phpcms _ ask 'a left join 'phpcms _ ask_posts' B ON. 'askid' = B. 'askid' WHERE (. status = 3 OR. status = 5) AND (LOCATE ('China',. 'title')> 0 and locate ('Beijing',. 'title')> 0) AND B. 'isask '= 1) UNION (SELECT. 'askid',. 'catid',. 'title',. 'status',. 'addtime',. 'reward ',. 'ancercount', B. 'message' FROM 'phpcms _ ask 'a left join 'phpcms _ ask_posts' B ON. 'askid' = B. 'askid' WHERE (. status = 3 OR. status = 5) AND (LOCATE ('Beijing',. 'title')> 0 and locate ('Tiananmen Square ',. 'title')> 0) AND B. 'isask '= 1) UNION (SELECT. 'askid',. 'catid',. 'title',. 'status',. 'addtime',. 'reward ',. 'ancercount', B. 'message' FROM 'phpcms _ ask 'a left join 'phpcms _ ask_posts' B ON. 'askid' = B. 'askid' WHERE (. status = 3 OR. status = 5) AND (LOCATE ('China',. 'title')> 0 or locate ('Beijing',. 'title')> 0 or locate ('Tiananmen Square ',. 'title')> 0) AND B. 'isask '= 1) LIMIT 0, 20

Phpcms_ask table: askid catid title tags reward userid username addtime endtime status flag answercount anonymity hits ischeck


Phpcms_ask_posts table: pid askid isask message addtime reply rptime candidate optimal reversion userid status anonymity username

Since you do not need to calculate the number of occurrences, the combination is not better.

SELECT. 'askid',. 'catid',. 'title',. 'status',. 'addtime',. 'reward ',. 'ancercount', B. 'message' FROM 'phpcms _ ask 'a left join 'phpcms _ ask_posts' B ON. 'askid' = B. 'askid' WHERE (. status = 3 OR. status = 5) AND B. 'isask '= 1 AND (LOCATE ('China',. 'title')> 0 or locate ('Beijing',. 'title')> 0 or locate ('Tiananmen Square ',. 'title')> 0)

To calculate the matching degree, you only need
Select sign (LOCATE ('China',. 'title') + sign (LOCATE ('Beijing',. 'title') + sign (LOCATE ('Tiananmen Square ',. 'title') as matching number from...

Since you do not need to calculate the number of occurrences, the combination is not better.
SQL code? 123 SELECT. 'askid',. 'catid',. 'title',. 'status',. 'addtime',. 'reward ',. 'ancercount', B. 'message' FROM 'phpcms _ ask 'a left join 'phpcms _ ask_posts' B ......

Thanks to the moderator, your method is very effective !! The speed is much faster now.

How can we sort matching degrees?

To calculate the matching degree, you only need
Select sign (LOCATE ('China',. 'title') + sign (LOCATE ('Beijing',. 'title') + sign (LOCATE ('Tiananmen Square ',. 'title') as matching number from...

Moderator: by specified ?? Word output in the field? ? How to implement sorting? Only query in one field