How to get the number of occurrence of each distinct value of GROUP CONCAT (php,mysql)

Question

I know this is something very easy to do but i have not gotten the correct way to do it. i found something Here but different from what i need and no active contribution yet. Someone kindly assist.

I have a table like this

name |status
-------------
mike |yes
mike |yes
mike |no
mike |ney
john |no
john |ney
john |yes

i want to output something like this

name |status           |total
------------------------------
mike |yes-2,no-1,ney-1 | 4
john |yes-1,no-1,ney-1 | 3

I tried using GROUP_CONCAT like this

    result = mysql_query("SELECT name, GROUP_CONCAT(DISTINCT status) AS status 
FROM table GROUP BY name ");
        while($row = mysql_fetch_array($result)){ 
    $st[] = $row['status'];
            $status=explode(",",$row['status']);
            $total = count($status);
            echo $row['name']."|".$row['status']."|".$total."<br><br>"; }

I would like to get the number of each distinct $row['status'] and if possible, a better way of arriving at $total.

EDIT1

name | yes | no | ney | total
------------------------------
mike |2    |1   |1    | 4
john |1    |1   |1    | 3

This second output was achieved Here

Answer 1

There is no need to use php as you can use pure SQL to get the desired result set:

SELECT name, GROUP_CONCAT(totalPerStatus) AS status, 
       (SELECT COUNT(*) FROM mytable WHERE name = t.name) AS total
FROM (
  SELECT name,      
         CONCAT(status, '-', COUNT(*)) AS totalPerStatus            
  FROM mytable
  GROUP BY name, status ) t
GROUP BY name;

The grouping by name, status performed in the sub-query gives you the count per 'status' per 'name'. Using CONCAT produces the following result set:

name    totalPerStatus
-----------------------
john    ney-1
john    no-1
john    yes-1
mike    ney-1
mike    no-1
mike    yes-2

The outer query uses GROUP_CONCAT on totalPerStatus to produce the required result set.

Demo here