Home Good way to map table based on members and months in year
Reply: 0

Good way to map table based on members and months in year

user1126 Published in June 19, 2018, 2:25 pm

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)

select m.*,
        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 ?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.340527 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO