CODEWITHSUNDEEP
mysqlsql
Reza Amya
Reza Amya

Reputation: 1724

Is there a way to count the LIKE results per row in MySQL?

I have a MySQL table jobs like this:

ID | title              | keywords
1  | UI Designer        | HTML, CSS, Photoshop
2  | Web site Designer  | PHP
3  | UI/UX Developer    | CSS, HTML, JavaScript

and I have a query like this:

SELECT * FROM jobs
WHERE title LIKE '%UX%' OR title LIKE '%UI%' OR title LIKE '%Developer%' OR keywords LIKE '%HTML%' OR keywords LIKE '%CSS%'

I want to sort results by most similarity.

for example for first row (ID 1), there is UI and HTML and CSS in the record row. then the number of CORRECT LIKE conditions is 3 for first row. same as this calculation, it is 0 for second row and it is 5 for third row.

then I want the result ordered by the number of CORRECT LIKE conditions, like this:

Results
ID | title              | keywords
3  | UI/UX Developer    | CSS, HTML, JavaScript
1  | UI Designer        | HTML, CSS, Photoshop

Then, is there anyway to count the number of similarities per row in query and sort the result like what I describe?

Upvotes: 1

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

You should not be storing keywords in a string like that. You should have a separate table.

If -- for some reason such as someone else's really, really, really bad design choices -- you have to deal with this data, then take the delimiters into account. In MySQL, I would recommend find_in_set() for this purpose:

SELECT j.*
FROM jobs j
WHERE title LIKE '%UX%' OR
      title LIKE '%UI%' OR
      title LIKE '%Developer%' OR
      FIND_IN_SET('HTML', REPLACE(keywords, ', ', '')) > 0 OR
      FIND_IN_SET('CSS', REPLACE(keywords, ', ', '')) > 0 
ORDER BY ( (title LIKE '%UX%') +
           (title LIKE '%UI%') +
           (title LIKE '%Developer%') +
           (FIND_IN_SET('HTML', REPLACE(keywords, ', ', '')) > 0) +
           (FIND_IN_SET('CSS', REPLACE(keywords, ', ', '')) > 0)
         ) DESC ;

This finds an exact match on the keyword.

You can simplify the WHERE, but not the ORDER BY, to:

WHERE title REGEXP 'UX|UI|Developer' OR
      FIND_IN_SET('HTML', REPLACE(keywords, ', ', '')) > 0 OR
      FIND_IN_SET('CSS', REPLACE(keywords, ', ', '')) > 0

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You could sum the matching resul in order by using if 

SELECT *

FROM jobs
WHERE title LIKE '%UX%' 
OR title LIKE '%UI%' 
OR title LIKE '%Developer%' 
OR keywords LIKE '%HTML%' 
OR keywords LIKE '%CSS%'
ORDER BY (title LIKE '%UX%'+ title LIKE '%UI%'+
       keywords LIKE '%HTML%'+ keywords LIKE '%HTML%') DESC

if return 1 or 0 so adding the true result you should obatin the most matching rows

Upvotes: 1

Related Questions