2

First and foremost, I don't even know how that query is called, but I bet it exists.

I got a table with events, and numbers representing the (fix)persons, whose are going to go to that event. Okay, then I got an user table, where these persons are, with the id-s from events. So events cell looks like "1,5,22,47", the person id-s.

The question is, how am I supposed to echo the event, with every persons name? :O Some like that:

"Event: Class trip with: Edward, Dr. Simons, Mrs. Walker"

Without having 315345345 queries...

Table examples:

Event:

  • id, name, date, people

Users:

  • id, name

so like: Event:

  • "1", "Class trip", "May 2", "1,2,5,6"
  • "2", "Museum visit", "May 14", "5,6,8,1"

Users:

  • "1", "Edward"
  • "2", "Dr. Simons"

3 Answers3

2

Create a third table UsersEvents (or something like that):

  • event_id
  • user_id,

Then add rows:

  • 1, 1
  • 1, 2
  • 1, 5
  • 1, 6

and

  • 2, 5
  • 2, 6
  • 2, 8
  • 2, 1

etc

Ada Lovelace
  • 835
  • 2
  • 8
  • 20
1

I would NOT recommend storing the relationship between an event and a list of attendees into a single column. This is very bad practice.

Instead, I would use a third table that mapped the relationship between events and attendees. In other words, create a One-to-Many relationship (master-detail, parent-child) using a foreign key.

Check out this post for more information regarding database design patterns.

With a One-to-Many table, you could write a simple SQL query to get the data you want. Something like:

SELECT Attendee FROM Event_Attendees WHERE Event LIKE $eventName

This would return an array of all the attendees for a particular event. Of course, you would want to use prepared statements using mysqli or PDO.

This Event_Attendees table would basically have two primary columns to establish the relationship: Event and Attendee. I generally use an auto-increment integer as a primary key index.

Community
  • 1
  • 1
David Wyly
  • 1,671
  • 1
  • 11
  • 19
  • 1
    Yes it does really work like that, I don't know why I didn't think on that... Probably I was trying to save too much space :) –  Apr 30 '15 at 21:58
0

You will need to create a function that you call for each row which concatenates all the names into one string and you execute like so

select EventId, EventName, udf_getPeople(EventId) FROM Event

Inside udf_getPeople

declare @everyone varchar(max);

Create a cursor to get the people sequentially related to EventId

fetch for each row

concatenate person fields to @everyone

close cursor when last person encountered

return everyone