CODEWITHSUNDEEP
phpmysql
Roman Klare
Roman Klare

Reputation: 11

PHP/MySQL complex search with more than one keywords

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.

for instance:

Keyword: "John Doe"

So my attempt was.

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

However: that gives me back all johns and does and Mr. John Doe is somewhere in that list, but not on top, even though it's supposed to be the most relevant result.

I also tried:

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

And that pretty much gives back the same result.

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......

I've been looking for 2 hours and i refuse to believe I'm the first person who ever tried to do that :-)

Any help is appreciated!

Thanks!

Upvotes: 1

Views: 803

Answers (3)

fyr
fyr

Reputation: 20859

Did you also try something like this 

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

see also

http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

This might be also a solution:

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

Upvotes: 3

amd
amd

Reputation: 21442

Boolean mode do not automatically sort rows in order of decreasing relevance so you have to :

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

Upvotes: 0

gbn
gbn

Reputation: 432200

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;

Upvotes: 0

Related Questions