I need to add these two buttons to the events pages of my website.
Basically, just like any other event page you might come across online, for example Facebook, you get the option to say whether you are attending or not attending the event.
And there is a section on the page with a number of people attending etc.
I would like to know the best solution to tackle this.
I'm not asking you to code it for me, just to point me in the right direction. I was thinking of something like, if the user clicks attending, their username or id is put into the database where the event exists and is added to an attending field in some kind of array format. Same for if they choose to not attend but in a notAttending field.
You're half way there, at least having the concept of a user and event entities - the only step of the puzzle that you're missing is the concept of an attendance entity that stores if the user has declined/is required/is attending/failed to turn up/etc. - so each user has an attendance for each event they're invited to; in database terms this is a many to many relationship (a user can be invited to many events, and an event can be attended by many users), so would typically live in a dedicated table.
I would create a table called rsvps or event_responses something to that effect.
It would have three columns:
attending should be NOT NULL. That way the only data in the table is generated in two situations:
If a user has not responded, they will not be in the table for that event. That you way you can easily get a count for "Attending", "Not Attending" and "Yet to Respond".
UI
Using jQuery, add two buttons to the event page: "Attending" and "Not Attending". Bind a click event to each and fire off an AJAX request when clicked. That way the interface is responsive.
Your idea sounds good. You could have a table named event_attendants having the columns event_id, user_id and status (don't forget the primary key). That's it. status could be a ternary property so you can have a facebook-like status yes, no and not sure or similar.
You can even do the button functionality with AJAX so it's a pretty quick UI.