Mysql - Select, order by times a column value appears in a string

Question

Sorry, I should have explained better: I have a $string and I want 'table' to be ordered by the number of times each value under 'word' column appears in that $string. for that i need a query like:

'SELECT * FROM table WHERE $string 
LIKE CONCAT("%",LOWER(word),"%")...

to find the words, then I would like to order them by appearance so that:

$string = "lollipop lollipop oh la le la le lollipop";
'SELECT * FROM table'
++++++++++table++++++++++
id - word
1  - la
2  - le
3  - lollipop
4  - shooby
+++++++++++++++++++++++++

(some query to table) would output:

++++++++++table++++++++++
id - word
1  - lollipop (appears 3 times)
2  - le (appears 2 times)
3  - la (appears 2 times)
4  - shooby (appears 0 times)
+++++++++++++++++++++++++

what's the query to do this? (btw it would be nice if the number of times each word appears in the string would show in the select table too).

Answer 1

If I follow what you're asking...

SELECT word, word_count
  FROM
  (SELECT word, COUNT(word) `word_count` FROM table GROUP BY word) order_table
  ORDER BY word_count DESC

Answer 2

One trick you can use is to replace the word in the original string. Then compare how many characters were lost. Divided by the length of the word, this gives you the total number of occurrences for that word. For example:

SELECT 
    w.word
,   (length(title) - length(replace(title, w.word, ''))) / length(word)
FROM (select 'lollipop lollipop oh la le la le lollipop' as title) t
CROSS JOIN (
    select 'la' as word
    union all select 'le'
    union all select 'lollipop'
    union all select 'shooby'
) w

-->

la          2.0000
le          2.0000
lollipop    3.0000
shooby      0.0000