Count items separated by semicolons in mysql table field

Question

I have a table in the mysql database where in the field tags values are stored in this format:

+-----------------+
| tags            |
+-----------------+
| B27;C38;H45;B27 |
| C38;H45         |
| B24;C45;L55;K98 |
+-----------------+

I need to count, via mysql or php, the single items in this column, row by row, so that the result brings me the count of the single items:

B27 -> 2
C38 -> 2
H45 -> 2
B24 -> 1
C45 -> 1
L55 -> 1
K98 -> 1

Already I thank you.

Answer 1

If I understand correctly, you will take the data and place it in an array without repeating the items.

I considered that the data will all be in a variable separated by ;, if it is not so you can join them all in a variable and wheels the code below.

Follow the code and php:

//Dados recebidos
$field = "B27;C38;H45;B27;C38;H45;B24;C45;L55;K98";

//Separa os dados em um array
$tags = explode(';', $field);

$countTags = array();

//Percorre o array e junta itens repitidos mostrando sua quantidade
foreach ($tags as $tag) {
    if(array_key_exists($tag, $countTags)){
        $countTags[$tag] += 1;
    }else{
        $countTags[$tag] = 1;
    }
}

//Mostra o array
var_dump($countTags);

Answer 2

Use the function explode(";", $tags) to remove the ';' to separate tags, and array_count_values() to count how many equal items are in the array:

<?php
$tags = 'B27;C38;H45;B27;C38;H45;B24;C45;L55;K98';
$array = array(explode(";", $tags));
var_dump(array_count_values($array[0]));

foreach (array_count_values($array[0]) as $key => $value) {
    echo $key." => ".$value."\n";
}