how to get an index and use it in query mysql

Question

I have a table similar to this:

   memberID  |    clubIDs     |       clubRegistrationDates     ...
--------------------------------------------------------------------
   2             3,4,10,2             2010,2011,2015,2014       ...
   3             2,1,5,6              2015,2000,2005,2010       ...
   4             3,2                  2014,2014                 ...

Meaning of a row is, for example: member 2 was registered to club 3 in 2010, was registered to club 4 in 2011 and so on...
So the query I want to get is to get any member who is a member of club 2 since 2014, so this query should get me members 2 and 4. I already have an sql but it is not connected to year parameter which is:

SELECT clubID, clubName,
(SELECT count(*) FROM members WHERE FIND_IN_SET('2',clubIDs) AND status='1' AND memberTypeID='2') AS activeNumber,
(SELECT count(*) FROM members WHERE FIND_IN_SET('2',clubIDs) AND status='0' AND memberTypeID='2') AS inactiveNumber
FROM clubs;

So I need to get club 2's index from clubIDs column and use it for clubRegistrationDates column. Any help is appreciated, thanks in advance.

Answer 1

Never store multiple values in a single column. This will only get you problems like yours now. A better table design would be

members table
-------------
id
name
...


clubs table
-----------
id
name
...


club_members table
------------------
club_id
member_id
registration_year

It is called a m to n relation. Then to get all members of club with id=2 since 2014 you can do

select m.id, m.name
from members m
join club_members cm on cm.member_id = m.id
where cm.club_id = 2
and cm.registration_year = 2014

And if you only have the club name and not the id then use

select m.id, m.name
from members m
join club_members cm on cm.member_id = m.id
join clubs c on cm.club_id = c.id
where c.name = 'Cool Club'
and cm.registration_year = 2014