Question

I m having the hardest time with a php (or mysql) search function. I d be willing to buy a script for that, but i can t find any.

I have a customer table (firstname, lastname, street, zip, city etc....) and i would like to be able to not just look for one keyword but for 2 IN 2 DIFFERENT columns.

例如:

关键词:“John Doe”

因此,我的尝试是。

SELECT ....
   WHERE CONCAT(firstname,lastname) LIKE  %john% 
   AND CONCAT(firstname,lastname LIKE  %doe%

然而,这给我留下了所有的 j子,John Doe先生是名单上的某个地方,但不是站在上,尽管它本应是最重要的结果。

I also tried:

....
   WHERE MATCH(firstname,lastname) AGAINST( %john doe% )

而这种结果恰恰相反。

So the result I m looking for would be:

1. John Doe (at first position!)
2. John Miller
3. John Smith
4. Harry Doe
5. Jack Doe
etc......

我looking了两小时,我拒绝相信我是最早试图这样做的人:

任何帮助都值得赞赏。

Thanks!

Answer 1

你们也试图这样做。

SELECT MATCH(firstname, lastname) AGAINST ( john doe ) as Relevance 
FROM table WHERE MATCH(firstname, lastname) AGAINST( john doe  IN
BOOLEAN MODE) 
HAVING Relevance > 0.3
ORDER BY Relevance DESC

另见《经济及社会理事会正式记录,2008年,补编第6号》(E/2008/44)。

rel=“nofollow”http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

这可能也是解决办法:

SELECT MATCH(firstname) AGAINST ( john doe ) as firstname_relevance, 
    MATCH(lastname) AGAINST ( john doe ) as lastname_relevance
    FROM table WHERE MATCH(firstname, lastname) AGAINST( john doe  IN
    BOOLEAN MODE) 
    ORDER BY firstname_relevance+lastname_relevance DESC

Answer 2

Like this?

SELECT
   firstname, lastname, othercol, MIN(Weighting)
FROM
    (
    SELECT firstname, lastname, othercol, 1 AS Weighting FROM...
    WHERE firstname =  john  AND lastname  =  doe 
    UNION ALL
    SELECT firstname, lastname, othercol, 2 AS Weighting FROM...
    WHERE firstname =  john  OR lastname  =  doe 
    ) T
GROUP BY
   firstname, lastname, othercol
ORDER BY
   MIN(Weighting) DESC;

Answer 3

诱杀模式不会自动地划分,从而降低相关性,因此,你必须:

SELECT MATCH(firstname,lastname) AGAINST( john doe ) as Relevance FROM table WHERE MATCH
MATCH(firstname,lastname) AGAINST( john doe   IN 
BOOLEAN MODE) ORDER 
BY Relevance DESC

友情链接