PHP/MySQL complex search with more than one keywords

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.

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!

fyr · Accepted Answer · 2012-01-13 11:30:50Z

3

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

8 Comments

Roman Klare Over a year ago

but still john doe isn't on first position. there some other "doe" having a higher relevance. i kinda don't get that

fyr Over a year ago

May you post the order in which the rows showed up with relevancy ? I also modified my query to exclude the % symbols as far as i know the wildcard in against queries is *

Roman Klare Over a year ago

Ute Schmidt 4.70247888565063 Pau Schmidt 4.70247888565063 Uwe Schmidt 4.70247888565063 Eva Schmidt 4.65077877044678

Roman Klare Over a year ago

#1191 - Can't find FULLTEXT index matching the column list my fulltext index contains both rows. is that wrong?

fyr Over a year ago

You might need to add two other index also for the alternate solution. One per each column.

|

gbn · Accepted Answer · 2012-01-13 11:04:42Z

0

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;

answered Jan 13, 2012 at 11:04

gbn

435k85 gold badges602 silver badges690 bronze badges

2 Comments

Roman Klare Over a year ago

yeah, but what if someone enters "doe john"?

gbn Over a year ago

@Roman Klare: update your question then. I answered what you asked

amd · Accepted Answer · 2012-01-13 11:20:31Z

0

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

edited Jan 13, 2012 at 11:20

answered Jan 13, 2012 at 11:06

amd

21.7k6 gold badges55 silver badges71 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL complex search with more than one keywords

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related