Photoshop Question posted in
The official user guide can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search