CODEWITHSUNDEEP
mysqlsql
david
david

Reputation: 3360

mysql non alphabetical order ?

I have this table :

id | businessName|  keywords
============================
 1      Adam         FOX
 2      FOX          bla bla bla

the user insert a string, I want to search this string in businessName and keywords columns.so I run this query:

SELECT * FROM `business` WHERE `businessName` LIKE '%str%' OR `keywords` LIKE '%str%'

my problem:

first I want to get the businesses where there names match the str, then get the businesses where there keywords match str.

i.e : the user enter the word FOX, mysql will return two businesses (adam and FOX), in this case I want the mysql to return at first FOX (because it's name match the str) then return Adam (because it's keywords match the str)

Upvotes: 3

Views: 378

Answers (1)

GolezTrol
GolezTrol

Reputation: 116100

You can order by a value that is generated based on the match:

SELECT 
  * 
FROM `business` 
WHERE 
  `businessName` LIKE '%str%' OR 
  `keywords` LIKE '%str%'
ORDER BY
  CASE WHEN `businessName` LIKE '%str%' THEN
    0
  ELSE
    1
  END

The example above is quite generic, but the following should also work in MySQL, just order by the condition:

SELECT 
  * 
FROM `business` 
WHERE 
  `businessName` LIKE '%str%' OR 
  `keywords` LIKE '%str%'
ORDER BY
  `businessName` LIKE '%str%' DESC

You would need to add DESC in the order by, since a match (true) will be ranked higher than no match (false).

Upvotes: 5

Related Questions