MySQL & PHP - Only list items not listed previously

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

you can group_by u.userid which may help.

Answer 2

I think i understand (not too sure though). try selecting a DISTINCT userid. That way a userid gets selected only once.

Answer 3

If you simply trying to figure out how many of each size shirt you need, then you should just find all of the people that are currently registered to help out in any event and then group by shirt size. Something like this:

SELECT size,count(*) FROM helpers,users WHERE helpers.user_id = users.user_id GROUP BY size;

Answer 4

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

Answer 5

I chose not to pivot the table for you, and to keep the query simple (for optimization), though long. I did it as three separate queries, one for each size, then joined them with UNION:

(SELECT MAX(e.eventid) AS eventid, MAX(e.event_name) AS event_name,
   MAX(p.tshirt_size) AS size, COUNT(*) AS tshirt_count
FROM Helpers h
JOIN Events e
  ON e.eventid = h.eventid
JOIN Profile p
  ON p.user_id = h.user_id
LEFT JOIN Events e2
  ON e2.eventid < e.eventid
JOIN Helpers h2
  ON h2.eventid = e2.eventid
  AND h2.user_id = h.user_id
WHERE p.tshirt_size = 'small'
  AND e2.eventid IS NULL
GROUP BY e.eventid)
UNION ALL
(SELECT MAX(e.eventid) AS eventid, MAX(e.event_name) AS event_name,
   MAX(p.tshirt_size) AS size, COUNT(*) AS tshirt_count
FROM Helpers h
JOIN Events e
  ON e.eventid = h.eventid
JOIN Profile p
  ON p.user_id = h.user_id
LEFT JOIN Events e2
  ON e2.eventid < e.eventid
JOIN Helpers h2
  ON h2.eventid = e2.eventid
  AND h2.user_id = h.user_id
WHERE p.tshirt_size = 'medium'
  AND e2.eventid IS NULL
GROUP BY e.eventid)
UNION ALL
(SELECT MAX(e.eventid) AS eventid, MAX(e.event_name) AS event_name,
   MAX(p.tshirt_size) AS size, COUNT(*) AS tshirt_count
FROM Helpers h
JOIN Events e
  ON e.eventid = h.eventid
JOIN Profile p
  ON p.user_id = h.user_id
LEFT JOIN Events e2
  ON e2.eventid < e.eventid
JOIN Helpers h2
  ON h2.eventid = e2.eventid
  AND h2.user_id = h.user_id
WHERE p.tshirt_size = 'large'
  AND e2.eventid IS NULL
GROUP BY e.eventid)
ORDER BY eventid, size = 'large', size = 'medium', size = 'small'

This will return results like this:

Event1       Small       1
Event1       Medium      0
Event1       Large       0
Event2       Small       0
Event2       Medium      0
Event2       Large       0
...

This query assumes that the events are ordered chronologically by eventid.