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"
Improve this question
2
  • Can you give a few sample rows from each table? Commented Apr 30, 2015 at 21:40
  • Are you allowed to change the structure? Commented Apr 30, 2015 at 21:52

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

1 Comment

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 :)
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.