I need to make simple table like this
This table represents yearly membership status for each month, it's super simple there is no advanced stuff there, that membership status is updated manually by admin (when member pays, admin update status for that month).
From database standpoint i have 2 simple tables like this:
As you can see i have membership table with member_id and date, and when payment is made that table is being populated.
So my question is: what is good way to map data to match that table ?
i'm thinking something like this (for example i want all members data from 2017 year)
GROUP_CONCAT(MONTH(ms.date)) as dates
from members m
left join membership ms on m.id = ms.member_id
where ms.date >= 01-01-2018
and ms.date <= 12-31-2018
this query should return list of members with dates array(array of numbers, each number represents month), so on UI side i could maybe generate this table view based on that ? Any suggestions for this, anyone have better solution ?