Need most popular value in mysql comma separated value?

Question

my tags table

| id  | tags         
| --- | ------------ 
| 1   | css,html,php 
| 2   | php,java,css      
| 3   | java,c++,ios 

need out put like

| tags  | tags         
| ---   | ------------ 
| css   |  2  
| php   |  2    
| java  |  1
| html  |  1
| c++   |  1
| ios   |  1

Answer 1

Not sure what DB extension you are using. You can try this -

// Fetch the data by executing- 
"SELEC GROUP_CONCAT(tags) tags FROM my_tags";

// Then explode them
$tags = $row['tags'];
$tags_array= explode(',', $tags);

//Count the values
$counts = array_count_values($tags_array);

// Sort
$counts = arsort($counts);

This is like an algorithm. Implement it with your code.

Answer 2

Try this.. Hope it will help.

SELECT       `value`,
             COUNT(`value`) AS `value_occurrence` 
    FROM     `my_table`
    GROUP BY `value`
    ORDER BY `value_occurrence` DESC
    LIMIT    1;