I have seen a question on this forum that I can relate with, but I can't apply the answers to my question.
Here it goes:
I would rename
members to make your table naming more consistent. Or
eventlist. Which ever you like more.
Then you want to define a many to many relation between members and events. This is done through an intermediate table which will reference both:
create table eventmembers ( id int unsigned not null primary_key auto_increment, member_id int unsigned not null references members(id), event_id int unsigned not null references events(id) )
I'm assuming that on your
events you already have
id fields which are set to be primary keys.
If you want to get all events attended by a specific user you can then do
select events.* from events left join eventmembers on events.id = eventmembers.event_id where member_id = ?
and get all the members in an event:
select members.* from members left join eventmembers on members.id = eventmembers.member_id where event_id = ?