dozvha2490
2012-02-21 21:37 阅读 44
已采纳

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

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

5条回答 默认 最新

  • 已采纳
    dongxianji0968 dongxianji0968 2012-02-21 22:35

    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

    点赞 评论 复制链接分享
  • doujindou4356 doujindou4356 2012-02-21 21:43

    you can group_by u.userid which may help.

    点赞 评论 复制链接分享
  • duanlu1279 duanlu1279 2012-02-21 21:46

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

    点赞 评论 复制链接分享
  • duanhe7471 duanhe7471 2012-02-21 21:46

    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;
    
    点赞 评论 复制链接分享
  • dongmibeng5885 dongmibeng5885 2012-02-21 23:01

    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.

    点赞 评论 复制链接分享

相关推荐