I have a simple events app where there are events and users, users can join multiple events. Each event is a single row in the events table. The users have there own users table.
I'm trying to find the easiest and economical way to store all those users that are attending an event. At the moment im thinking of storing the users id in a attendees field on the events table - i would have to separate them with a comma.
I'm find with that part but im struggling to think of a way to read this field, split them into an array and then fetch the full name of each user.
Hope that makes sense.
Don't do that. MySQL is a relational database model, so go ahead and use relations. In that case, create a new attendance table with two columns: user id and event id.
It will make your tasks even easier; if you need to find all events for one user it's as simple as finding all users for one event.
For example, to get all attendees' names, it would be something like
SELECT u.name
FROM user u
INNER JOIN attendance a ON a.userid = u.id
INNER JOIN event e ON a.eventid = e.id
WHERE e.id = <youreventid>;
you can use json format like this
$events=array('evnt1','evnt2'............,'eventn');
$tostore=json_encode($events); // op will in json string format
in mysql query use $tostore to store data
to use further you can use
$allevents=json_decode($evemtsfromdb);
it will return the array of events
Using explode is the quick and simple way to do it using your comma-separated description.
$attendees = explode(',', $db_result['attendees']);
foreach ($attendees as $attendee_id)
{
// code here to get attendee name
}
Although personally I started avoiding storing data this way and started using extra tables where necessary so that I could pull all the data I need in one query using joins and such. In your case, this would be storing attendees in a separate tables like:
attendee_id | event_id
----------------------
3 | 1
4 | 1
6 | 2