MYSQL Relationships, Indexes and Keys...will pay $ if you help me...seriously!!

[ryan656]

Hello All,

 

I am farily new to PHP/MYSQL and my company has just put me on a client requiring a social networking site. 

Ive gotten a long pretty good (user registration, login, profile page, etc..) but Im stuck now.

 

I need to integrate 3 sections (tables in the database) of the site: the Members, the Groups and the Events sections.

Basically the admins are going to create groups which members can join, then the admins are going to associate events with these groups and charge $ for tickets to the events.

 

So I need a way to keep track of all the users in a certain group and all the users who have RSVP'd for an event.

 

The natural thing that comes to mind is having an array as an field in the groups or events table with the id of any member thats in that group or attending the event.

I dont think that is possible with mysql, or any database actually so the next logical thing is using foreign keys inside the events/groups tables that relate to the member id's.

 

Is that something that can be accomplished.  I am basically open for any ideas that will help me get this done.

 

I WILL GLADLY PAY ANYONE WHO CAN HELP ME THROUGH THIS PROBLEM, I AM A FAST LEARNER SO YOU WONT BE WASTING YOUR TIME>

 

THANKS

 

Ryan

 

ryan@gomodi.com

[bubblegum.anarchy]

The three core tables:

 

member.id

member.name

 

event.id

event.name

 

group.id

group.name

 

A table to track events rsvp:

 

event_rsvp.id

event_rsvp.event_id

event_rsvp.date

 

A table to track members in groups:

 

member_in_group.member_id

member_in_group.group_id

 

To get a list of all members and their associated groups:

 

SELECT member.id
    , member.name
    , group_concat(group.name SEPARATOR '\n') AS groups
FROM member
    LEFT JOIN member_in_group ON member.id = member_in_group.member_id
    LEFT JOIN group ON member_in_group.group_id = group.id
GROUP BY member.id
ORDER BY member.name

 

To get a list of all members that have rsvp'd an event:

 

SELECT member.id
    , member.name
    , event.name
    , event_rsvp.date
FROM event
     INNER JOIN event_rsvp ON event.id = event_rsvp.event_id
     INNER JOIN member ON event_rsvp.member_id = member.id
WHERE event.id = $event_id;

[Barand]

I was thinking a useful query would be "members who have NOT rsvp'd to an event", but that would require knowing which members were invited in the first place.

 

As invite is by group, I suggest another table of invited_groups, associating invited groups with events (or invited_members, associating invited members with events) if you prefer it at that level.

[bubblegum.anarchy]

To get a list of all members that have NOT rsvp'd an event:

 

SELECT member.id
     , member.name
     , event.name
     , event_rsvp.date
FROM member
     LEFT JOIN event_rsvp ON event_rsvp.member_id = member.id
     LEFT JOIN event ON event.id = event_rsvp.event_id AND event.id = $event_id
WHERE event_rsvp.member_id IS NULL;

 

something like that anyway.

[Barand]

That was my first thought, but it assumes all members are invited to an event

 

OR change events_rsvp into events_invited table and update with RSVP_date when they reply?

 

PS. Then you have a means of issuing invitations in the first place.

[bubblegum.anarchy]

yeah... event_rsvp would certainly make a good double for an invitation record with date set to null as default making the NOT rsvp'd query a great deal simpler:

 

event_rsvp.id

event_rsvp.event_id

event_rsvp.member_id <= forgot this member earlier

event_rsvp.date

 

SELECT  member.id
     , member.name
     , event.name
FROM event_rsvp
     INNER JOIN event ON event_rsvp.event_id = event.id
     INNER JOIN member ON event_rsvp.member_id = member.id
WHERE event_rsvp.event_id = $event_id AND event_rsvp.date IS NULL

 

much better!