How to sort an mysql Result?

Table :

 id        title

1.         php is a good language.
2.         Java is a good language
3.         All language are good
4.         Php,Java both good language
5.         Php is good but Java is not

Question

string: $#

$string = "php Java language"

Result Order

php ,Java both  good language
-------------------------
php is good Java is not

-------------------------
php is good language 
---------------------------
Java is good language
------------------------
all language are good

I have no idea how to achive the order list by how many words are matched in string.

It will be great if you explain the query.

Thank you.

Answer

Convert your string to JSON array then

SELECT test.id, test.tittle
FROM test
LEFT JOIN JSON_TABLE(@criteria, 
                     "$[*]" COLUMNS (word VARCHAR(255) PATH "$")) jsontable ON LOCATE (jsontable.word, test.tittle)
GROUP BY test.id, test.tittle
ORDER BY SUM(jsontable.word IS NOT NULL) DESC

@criteria is a placeholder for JSON array mentioned above.

Of course, you may provide your criteria as space-separated string and convert it to JSON in the query using string functions.

---

The version without JSON usage:

WITH RECURSIVE
cte AS (SELECT test.id, 
               test.tittle, 
               0 amount, 
               TRIM(TRIM(LEADING SUBSTRING_INDEX(@criteria, ' ', 1) FROM @criteria)) criteria, 
               SUBSTRING_INDEX(@criteria, ' ', 1) token
        FROM test
        UNION ALL
        SELECT id, 
               tittle,
               amount + (LOCATE(token, tittle) > 0),
               TRIM(TRIM(LEADING token FROM criteria)),
               SUBSTRING_INDEX(criteria, ' ', 1)
        FROM cte
        WHERE TRIM(criteria) != '' )
SELECT id, tittle
FROM cte
GROUP BY id, tittle
ORDER BY MAX(amount) DESC;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3ab611ed7429e23be2abfcb8b0186e6d