count row values separated by comma

Question

I have a MySQL DB with multiple rows and a column that contain some text, separated with comma.

For a quick idea, my DB look like this:

id	tag
1	wordpress, psd , html
2	wordpress, html
3	wordpress
4	psd

Output need to be like this:

tag	count
wordpress	3
psd	2
html	2

i try this

<?php
select tag, count(id) FROM tableee GROUP BY tag
?>

and i try this

<?php
$query = mysqli_query($con, "SELECT tag FROM tableee   ");   
$convert_to_array = explode(',' , $row['tag'] );
$key_value = explode('=>', $convert_to_array [2]);  
echo count($key_value );
?>

and i try this

<?php       
    $query = mysqli_query($con, "SELECT tag FROM tableee   ");
    
    while($row=mysqli_fetch_assoc($query))
    
    {           
        $array = explode(" " ,$row['tag']);
    
        $array2=array();
    
        for($i = 0; $i<count($array); $i++)
    
        {
            array_push($array2, $array[$i] );
        }   
        print_r(     count($array2)       ) ;
    }
?>

this Problem is solved by nbk

<?php
$query = mysqli_query($con, "SELECT tag FROM tableee   ");
$array2=array();
while($row=mysqli_fetch_assoc($query))

{           
    $array = explode("," ,$row['tag']);

    

    for($i = 0; $i<count($array); $i++)

    {
        array_push($array2, trim ($array[$i]) );
    }   
    

}
$array3 = array_count_values($array2);
$array4 = array_keys($array3);
print_r(   $array4[0] ) ;
print_r( $array3[ $array4[0]]);
?>

nbk · Accepted Answer · 2021-07-03T18:37:04.337

1

You need to intialize the array2 before you start the while, else you add always a new array to it

to count array you need array_count_values

And also you need to trim the splitet data, to remove all leading and trailing spaces

Last to split you need a comma not a space

<?php
$query = mysqli_query($mysqli, "SELECT tag FROM tableee   ");
$array2=array();
while($row=mysqli_fetch_assoc($query))

{           
    $array = explode("," ,$row['tag']);

    

    for($i = 0; $i<count($array); $i++)

    {
        array_push($array2, trim ($array[$i]) );
    }   
    

}
$array3 = array_count_values($array2);
$array4 = array_keys($array3);
print_r(   $array4[0] ) ;
print_r( $array3[ $array4[0]]);

edited Jul 03 '21 at 18:37

answered Jul 03 '21 at 17:34

nbk

45,398
8
30
47

Hi nbk , thanks, but I want to count and display number of WordPress 3 and psd 3 and html 3 like this echo count( $array[0] ) ; echo count( $array[1] ) ; echo count( $array[2] ) ; – mehdi ouatmane Jul 03 '21 at 18:08
the result is an array with exactly that information and you can output like any other array – nbk Jul 03 '21 at 18:11
Besides you can debug your code and array2 has all the values from the table so it would have more than 3 in your case 7 – nbk Jul 03 '21 at 18:16
But show me the result Array ( [wordpress] => 3 [psd] => 2 [html] => 2 ) – mehdi ouatmane Jul 03 '21 at 18:17
Array ( [wordpress] => 3 [psd] => 2 [html] => 2 ) – mehdi ouatmane Jul 03 '21 at 18:18
I just want the number to appear like wordpress 3 – mehdi ouatmane Jul 03 '21 at 18:19
no it doesn't run the code https://phpize.online/?phpses=2cecee6788f99297e0ff793a1a9409f1&sqlses=927e49fc9fcbad1fda93c5fae9919ac9&php_version=php7&sql_version=mysql80 – nbk Jul 03 '21 at 18:19
It works but I want to show WordPress number alone – mehdi ouatmane Jul 03 '21 at 18:24
i added the code to display only one 1 line with wordpress see https://phpize.online/?phpses=43d7b6b612fd500a70ee1352020a51c6&sqlses=927e49fc9fcbad1fda93c5fae9919ac9&php_version=php7&sql_version=mysql80 – nbk Jul 03 '21 at 18:37
thanks you very much my friend this Problem is solved – mehdi ouatmane Jul 04 '21 at 16:31

score 0 · Answer 2 · answered Jul 03 '21 at 16:27

0

I'm not sure about your question but. You can do an explode of your row, and count the number of iteration.

    <?php
foreach($dataToCount as $dataToExplode){
      $explodeToCommas=explode(',',$dataToExplode);
      //To understand the function explode do:
       var_dump($explodeToCommas);
       //count every iteration
        $countIteration=count($explodeToCommas);
}

answered Jul 03 '21 at 16:27

RONIN_FR

26
3

hi RONIN_FR sorry did not work – mehdi ouatmane Jul 03 '21 at 16:48

score 0 · Answer 3 · answered Jul 03 '21 at 20:05

You can directly achieve this in MYSQL by using feature in MYSQL known as 'char_length'.

$query = mysqli_query($con, "SELECT   id,  tag, 
  (CHAR_LENGTH(tag) - CHAR_LENGTH(REPLACE(tag, ',', '')) + 1) as count 
FROM tableee   ");

while($row=mysqli_fetch_assoc($query))

{           
   echo '<td>'.$row ["id"].'</td>';

echo '<td>'.$row["count"].'</td>';  
    
}

count row values separated by comma

3 Answers3