Storing multiple userids in a single field

Question

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.

you can store the array of all events in json_encode format and can use json_decode when needed — Manoj Dhiman, Mar 26 '15 at 13:16
On the PHP side you could always just `explode()` the table data, loop through the array and fetch each users details. — icecub, Mar 26 '15 at 13:16

Paul · Accepted Answer · 2015-03-26T13:23:30.153

3

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>;

edited Mar 26 '15 at 13:23

answered Mar 26 '15 at 13:19

Paul

8,974
3
28
48

I should of thought of this myself really, bit daft. – Nathan Mar 26 '15 at 13:21

score 0 · Answer 2 · answered Mar 26 '15 at 13:18

0

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

answered Mar 26 '15 at 13:18

Manoj Dhiman

5,096
6
29
68

I've you ever need to store several values in one single field in `MySQL`, don't `json_encode()` them but rather `serialize()` them. Even if you do so, this is not a proper way to store data in `MySQL`. – D4V1D Mar 26 '15 at 13:20
Actually, both would be fine but with pros and cons. Anyhoo, I'd rather go with `serialize()` if data needs to be retrieved with `PHP`. Check this out: http://stackoverflow.com/a/5351788/2788131 – D4V1D Mar 26 '15 at 13:26
yes serialize also can be a option . there are a couple of ways to do this . – Manoj Dhiman Mar 26 '15 at 13:29

score 0 · Answer 3 · answered Mar 26 '15 at 13:21

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

Storing multiple userids in a single field

3 Answers3