CODEWITHSUNDEEP
mysqlrelevance
Nitx96
Nitx96

Reputation: 5

Rank PHP MySQL search results based on result sequence

I'd like to improve my MySQL search functionality. When searching "Fi" in my car database, I would like FIAT to be ranked first, as I feel a cars brand should have a higher rank than the model version. I want to tell MySQL if the search term matches the first letters of a Brand then prioritise that result first.

{My Search results]1

Here's my current code

$sql = "SELECT v.id, v.model, v.model_version, v.model_year, b.brand FROM vehicles v LEFT JOIN brands b ON v.brand_id = b.id WHERE CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE ? LIMIT 6";

Upvotes: 0

Views: 427

Answers (2)

Nitx96
Nitx96

Reputation: 5

I found the best answer that works really well I believe. As full-text searches only index up to 4 characters (unless you change server settings) this can be a hassle.

Here was my approach:

SELECT v.id, v.model, v.model_version, v.model_year, b.brand FROM vehicles v
          LEFT JOIN brands b ON v.brand_id = b.id
          WHERE (
            CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE 'fi%' OR
            CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE '%fi%' OR
            CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE '%fi'
          ) ORDER BY case
              WHEN CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE 'fi%' THEN 1
              WHEN CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE '%fi%' THEN 2
              WHEN CONCAT(b.brand, ' ', v.model, ' ', v.model_version) LIKE '%fi' THEN 3
            ELSE 4 END
          LIMIT 6

Upvotes: 0

user9706
user9706

Reputation:

The comment by @user3783243 is the right answer. If you want to do it with mysql, define a rank column along these lines: 4 * if(b.brand like ?, 1, 0) + 2 * if(v.model like ?, 1, 0) + if(v.model_version like ?, 1, 0) as 'rank'. It gets tricky quickly. Let's say there is exact mach on the model but a partial match on the brand. Should the partial brand really be ranked higher?

Upvotes: 0

Related Questions