MySQL和PHP - 仅列出之前未列出的项目

Question

Sorry the title is very vague.

Basically, I'm stuck with something. I have an ample working script which lists from two tables.

It pulls a list of events

Event 1
Event 2
Event 3
Event 4
Event 5
Event 6

Now, users need t-shirts for the "overall" event. They are volunteers, and they have t-shirts. When they submit which events that can help steward, they choose their t-shirt size so I can order them.

I then get a list

Event 1:
Small = Count
Medium = Count
Large = Count

When the volunteer enters their t-shirt size, it is saved in their PROFILE. The events are stored in a table called events.

However, that part isn't important. I can easily get it to list as above and it does count correct, to a certain extent. The problem is, say I have a volunteer called Bob. Bob's t-shirt size is Small. He is only volunteering for Event 1. NO OTHER VOLUNTEERS HAVE ENTERED THE DAYS THEY CAN HELP YET. So I would have all events listed, and the only area that would equal anything other than 0 is

Event 1 - Small = 1, Medium = 0, Large = 0 Event 2 - Small = 0, Medium = 0, Large = 0 etc. etc....

Anyway, say Bob decides to help at Event 1,2,3 and 5.

It is then showing Event 1 - Small = 1, Medium = 0, Large = 0
Event 2 - Small = 1, Medium = 0, Large = 0
Event 3 - Small = 1, Medium = 0, Large = 0
Event 4 - Small = 0, Medium = 0, Large = 0
Event 5 - Small = 1, Medium = 0, Large = 0
Event 6 - Small = 0, Medium = 0, Large = 0

It's showing that because the query is basically

SELECT u.id, u.userid, u.eventid, u.helping, i.userid, i.tshirt_size FROM helpers AS u LEFT JOIN profiles AS i ON u.userid=i.userid WHERE `eventid`='$eventid' AND u.helping='1' AND i.tshirt_size='$size'

I then echo that with a function, like getTShirtCount("eventid","Small");

Event ID is picked up by a MySQL Fetch Array loop, looping the Events I have in my database (which is also where the event ID is picked up from).

Event 1 = ID: 1
Event 2 = ID: 2
etc..

When they submit the events they can help, it goes into a "helpers" table, like UserID = 101
EventID = 1
Helping = 1

UserID = 101
EventID = 2
Helping = 1

UserID = 101
EventID = 3
Helping = 1

UserID = 101
EventID = 4
Helping = 0

etc...

== WHAT I WANT TO DO ==

IF the user, Bob, has been listed in Event 1 - I don't need him to be counted in ANY OTHER EVENT. The count is so that I can bring the correct amount of t-shirts to each event to distribute. So it may tell me I need 50 small, 10 large, 40 medium.

If Bob is attending Event 1, he will get his t-shirt then, so he doesn't need me to bring another (thus be counted) in any other event.

So, I am essentially needing it to ONLY add 1 to the num_rows count, IF "Bob" has no "Helping='1'" for any previous events.

I hope I've explained that well enough.

Can anyone help?

Answer 1

I had to assume your events table has fields eventID and event_name, I could guess other tables from the query in your question.

SELECT e.event_name, 
  SUM( eh.shirt_size <=> 'Small' ) as Small,
  SUM( eh.shirt_size <=> 'Medium' ) as Medium,
  SUM( eh.shirt_size <=> 'Large' ) as Large
FROM events e
LEFT JOIN
  ( SELECT h.userID, MAX(p.tshirt_size) as shirt_size, MIN(eventID) as first_event 
    FROM helpers h
    JOIN profiles p ON p.userID = h.userID
    WHERE h.helping = 1
    GROUP BY h.userID ) eh
  ON e.eventID = eh.first_event
GROUP BY e.event_name

A working example: http://sqlfiddle.com/#!2/33f9b/1