CODEWITHSUNDEEP
phpmysql
Ravi Kadia
Ravi Kadia

Reputation: 275

Need most popular value in mysql comma separated value?

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

Upvotes: 5

Views: 160

Answers (3)

Sougata Bose
Sougata Bose

Reputation: 31749

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.

Upvotes: 2

Akhil VL
Akhil VL

Reputation: 357

This may help: select group_concat(tags) from tags group by tag;

Upvotes: 1

Rahul
Rahul

Reputation: 726

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;

Upvotes: 1

Related Questions