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?
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
you can group_by u.userid which may help.
I think i understand (not too sure though). try selecting a DISTINCT userid. That way a userid gets selected only once.
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;
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.