Q:

How to count each item in array with sql

I have a user table which contain a membergroupids, and user table looks like this: 
userid   membergroupids
1        1,2
2        2,3
3        2,3,4

and I want to use sql to output a result like this 
membergroupid    count
1                1
2                3
3                2
4                1

I tried use SELECT membergroupids FROM user, then use php to loop through the result and get the count, but it works with small set of user table, but I have a really big user table, the select query itself will take more than 1min to finish, is there better way to do this? 

A:

There is a much better way to do it. Your tables need to be normalized:
Instead of
userid   membergroupids
1        1,2
2        2,3
3        2,3,4

It needs to be
userid   membergroupids
1        1
1        2
2        2
2        3
3        2
3        3
3        4

From here, it's a simple query to get the counts (assuming this table is called your_table:
select count(membergroupids) as numberofgroups, userid 
from your_table 
group by userid 
order by userid

The real problem, then, is getting your tables normalized.  If you only have 9 membergroupids, then you could use a like '%1%' to find all userids with membergroupid #1.  But if you have 10, then it won't be able to distinguish between 1 and 10.  And sadly, you can't count on the commas to help you distinguish because the number might not be surrounded by commas.
unless...
Create new field with group ids encapsulated by commas
you could create a new field and populate it with membergroupids and surround it with commas by using concat (check your database's docs). Something along this line:
update your_table set temp=concat(',', membergroupids, ',');

This could give you a table structure like so:
userid   membergroupids  temp
1        1,2             ,1,2,
2        2,3             ,2,3,
3        2,3,4           ,2,3,4,

Now, you have the ability to grab distinct member group ids in the new field, ie, where temp like '%,1,%' to find userids with membergroupid 1. (They will be encapsulated by commas) Now, you can manually build your new normalized table which I'll call user_member.
Insert membergroupid 1:
insert into user_member (userid,membergroupid) select userid,'1' from your_table where temp like '%,1,%';

You could make a php script that loops through all the membergroupids.
Keep in mind that like %...% is not very efficient, so don't even think about relying on this to do your count.  It'll work, but it's not scalable. It would be much better to use this to build the normalized table.

