Count number of users for the comma separated values

Question

I have comma-separated field base_users in my database. How do I query to count the totaluser of that group? I have no problem to calculate the totaluser if the data is not in comma-separated field.

SELECT COUNT(base_u_id) AS totaluser 
FROM base_users 
WHERE base_u_group =".$row['base_gp_id']."

1)base_users
|base_u_id |  base_u_name  | base_u_group |
------------------------------------------
|    1     |   username1   |   1, 2, 4    |
|    2     |   username2   |   3          |
|    3     |   username3   |   3, 4       |
|    4     |   username4   |   1, 4       |

2)base_groups                           
| base_gp_id | base_gp_name  | 
------------------------------
|    1       |    group1     |
|    2       |    group2     |   
|    3       |    group3     | 
|    4       |    group4     |
|    5       |    group5     |

From the sample database above, my expected result will be:

Total User of group1 = 2
Total User of group2 = 1
Total User of group3 = 2
Total User of group4 = 3
Total User of group5 = 0

This is what I have tried so far:

<?php
$getUser = base_executeSQL("SELECT * FROM base_users");

while($row_getUser = base_fetch_array($getUser))
{
    $explodeData = explode(", ",$row_getUser['base_u_group']);
    foreach($explodeData as $data)
    {
        $getUserGroupSQL = base_executeSQL("SELECT COUNT(base_u_id) AS totaluser FROM base_users as user, base_groups as gp WHERE gp.base_gp_id ='".$data."' ");

        while($UserGroupProfile_row = base_fetch_array($getUserGroupSQL))
        if (base_num_rows($getUserGroupSQL)!= 0)
            $totaluser = $UserGroupProfile_row["totaluser"];
        elseif (base_num_rows($getUserGroupSQL)== 0)
            $totaluser = 0;
    }
}
?>

The total is simply the number of commas + 1 (or the difference between the length of the string with commas and the length of the string without commas, + 1). Now see normalisation — Strawberry, May 14 '15 at 07:24
@Strawberry I found [this](http://stackoverflow.com/questions/7020001/how-to-count-items-in-comma-separated-list-mysql). Did you mean by this? — Sollo, May 14 '15 at 09:02
Probably, but I misunderstood the question. The only sensible solution is normalisation (or forget about using SQL). — Strawberry, May 14 '15 at 09:04

score 0 · Answer 1 · answered May 14 '15 at 07:47

0

Try this:

SELECT COUNT(u.base_u_id), g.base_gp_name FROM base_users u
INNER JOIN base_groups g ON IF(POSITION(',' IN u.base_u_group) > 0, u.base_u_group LIKE ('".$row['base_gp_id'].",%') OR u.base_u_group LIKE ('%, ".$row['base_gp_id'].",%') OR u.base_u_group LIKE ('%, ".$row['base_gp_id']."'), u.base_u_group = '".$row['base_gp_id']."')
WHERE g.base_gp_id = ".$row['base_gp_id']."

answered May 14 '15 at 07:47

Serge

417
2
12

This doesn't work for me. It only consists of 1 user. – Sollo May 14 '15 at 07:59

score 0 · Accepted Answer · answered May 14 '15 at 07:48

Use below logic: Just HINT

$group = array(1=>0,2=>0,3=>0,4=>0,5=>0);
$base_u_group = array('1,2,4','3','3,4','1,4');
foreach($base_u_group as $gr) {
       $split = explode(',', $gr);
       if (!empty($split)) {
          foreach($split as $val) {
            if ($val) {
              $group[$val] =  $group[$val] + 1;
            }
          }
       }
}
var_dump($group);

Count number of users for the comma separated values

2 Answers2