CODEWITHSUNDEEP
phpmysqlsorting
user756476
user756476

Reputation: 83

Weighting MySql query searches

I've had a look around and I have found a few questions like mine but they lack an explanation.

I'm trying to search a table with multiple columns. I want to have the rows with the highest number of match columns at the top and the ones with the least at the bottom. I've seen a few ways of doing. My current and awful way is with lots of MySQLs querys and PHP doing the work.

Example

    +----+-----------+----------+------+-------+
    | ID | firstName | lastName | more | stuff |
    +----+-----------+----------+------+-------+
    |  1 | Bob       | Hope     |    1 |   450 |
    |  2 | Steve     | Hope     |    0 |    29 |
    |  3 | Gary      | Flops    |    1 |     8 |
    +----+-----------+----------+------+-------+

I want to be able to search for lastName = Hope OR more = 1 I would like Bob Hope to be at the top because he matches two of the requested things. I know in this example this is what will happen but this is just an example.

If I query; lastName = Hope OR firstName = Steve. Steve should be at the top followed by Bob

I hope this is easy to understand. Can some give me a detailed example with explanation.

Upvotes: 2

Views: 267

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Besides Mike's great answer, here's another option:

SELECT
    id,
    firstName,
    lastName,
    more,
    stuff,
    COUNT(m1.id) + COUNT(m2.id)
      AS weight
FROM
    mytable AS m

      LEFT JOIN
    mytable AS m1
        ON  m1.id = m.id
        AND m1.lastName = 'Hope'

      LEFT JOIN
    mytable AS m2
        ON  m2.id = m.id
        AND m2.firstName = 'Steve'  

GROUP BY m.id
HAVING COUNT(m1.id) + COUNT(m2.id) > 0    
ORDER BY weight DESC;

and one more:

SELECT
    m.id,
    m.firstName,
    m.lastName,
    m.more,
    m.stuff,
    COUNT(*) AS weight
FROM
    mytable AS m
      JOIN
        ( SELECT id
          FROM mytable
          WHERE lastName = 'Hope'
        UNION ALL
          SELECT id
          FROM mytable
          WHERE firstName = 'Steve'  
        ) AS c
        ON c.id = m.id
GROUP BY m.id  
ORDER BY weight DESC;

Upvotes: 1

Mike
Mike

Reputation: 21659

How about this:

SELECT
  id,
  firstName,
  lastName,
  more,
  stuff,
  (lastName = 'Hope') + (firstName = 'Steve') AS weight
FROM mytable
ORDER BY weight DESC;

Result:

+----+-----------+----------+------+-------+--------+
| id | firstName | lastName | more | stuff | weight |
+----+-----------+----------+------+-------+--------+
|  2 | Steve     | Hope     |    0 |    29 |      2 |
|  1 | Bob       | Hope     |    1 |   450 |      1 |
|  3 | Gary      | Flops    |    1 |     8 |      0 |
+----+-----------+----------+------+-------+--------+

Upvotes: 5

Jacob
Jacob

Reputation: 43219

The easiest way I can think of is with MATCH() ... AGAINST. You can sort by "score".

SELECT ID, firstName, LastName, stuff, more, MATCH(firstName,LastName,more,stuff) AGAINST ('words to search for'  IN BOOLEAN MODE) AS score
FROM yourTable 
WHERE MATCH(firstName,LastName,more,stuff) AGAINST ('words to search for'  IN BOOLEAN MODE)
ORDER BY score

Problem with this is that it will be slow without a fulltext index and a fulltext index is only available with MyISAM engine.

Upvotes: 1

Related Questions