Tally number of people in 2 groups? Then display?

  • Betroffene Version
    WoltLab Suite 3.1

    I was wondering if someone that is better at java and php than me, could give me a hand.

    I am looking for a code snippet, to give me a members number. What I am looking to do, is list all members from 2 groups, IE, if they are in group 1, and also in group 2, then count them as 1. Then list the number of total users that are in both group 1 and 2, so that I can display it.

    I'm not trying to list the actual members, just how many members are in groups 1 and 2.

    Thanks in advance :)

  • SQL
    SELECT userID, username FROM wcfN_user WHERE userID IN (SELECT userID FROM wcfN_user_to_group WHERE groupID = FIRST_GROUP_ID) AND userID IN (SELECT userID FROM wcfN_user_to_group WHERE groupID = SECOND_GROUP_ID);

    (untested)

    this allows you to receive the datasets from the database.

  • An alternate solution with just two instead of three queries is:

    SQL
    SELECT userID, username
    FROM wcfN_user
    WHERE userID IN 
       (SELECT userID 
        FROM wcfN_user_to_group 
        WHERE groupID IN (FIRST_GROUP_ID, SECOND_GROUP_ID) 
        GROUP BY userID 
        HAVING COUNT(groupID)=2
    );

    If you really only need the userID and not the username you can even shorten it to:

    SQL
    SELECT userID 
    FROM wcfN_user_to_group 
    WHERE groupID IN (FIRST_GROUP_ID, SECOND_GROUP_ID) 
    GROUP BY userID
    HAVING COUNT(groupID)=2;

    2 Mal editiert, zuletzt von Barungar (8. Januar 2019 um 10:44)

  • cyberlocc

    Or perhaps did we (jens1o and me) missunderstand your request? After re-reading your post I think this solution (not optimized) might be what you wanted:

    This will output something like:

    Code
    +----+----+----+
    | A  | B  | AB |
    +----+----+----+
    | 11 | 48 |  7 |
    +----+----+----+

    Which essentially means: 11 users are in FIRST_GROUP, 48 users are in SECOND_GROUP and 7 users are in FIRST_GROUP as well as in SECOND_GROUP.

    Which is allways the full count of users...

  • cyberlocc

    Or perhaps did we (jens1o and me) missunderstand your request? After re-reading your post I think this solution (not optimized) might be what you wanted:

    This will output something like:

    Code
    +----+----+----+
    | A  | B  | AB |
    +----+----+----+
    | 11 | 48 |  7 |
    +----+----+----+

    Which essentially means: 11 users are in FIRST_GROUP, 48 users are in SECOND_GROUP and 7 users are in FIRST_GROUP as well as in SECOND_GROUP.

    Which is allways the full count of users...

    This one was what I was after :) thank you.

  • cyberlocc

    Or perhaps did we (jens1o and me) missunderstand your request? After re-reading your post I think this solution (not optimized) might be what you wanted:

    This will output something like:

    Code
    +----+----+----+
    | A  | B  | AB |
    +----+----+----+
    | 11 | 48 |  7 |
    +----+----+----+

    Which essentially means: 11 users are in FIRST_GROUP, 48 users are in SECOND_GROUP and 7 users are in FIRST_GROUP as well as in SECOND_GROUP.

    Which is always the full count of users...

    Not to sound dumb, but I been trying to play with this, and have no idea how to make it display like you did lol.

    Also thats a little more than what I needed, Basically, I want it to check for people that are in Groups 1 and 2, and then print how many people are in them both. I dont need to print how many people are in 1 or 2, just how many are in both, and then print the number.

  • That display is the output of the sql command console. That's also the reason for those fancy ascii table art. ;) I like to interact with my database simply by using the mysql command via a login ssh shell.

    If you really just want one number this might be what you want:

    SQL
    SELECT COUNT(userID) AS number
    FROM
    (
       SELECT
       userID, COUNT(groupID)
       FROM wcfN_user_to_group
       WHERE groupID IN (FIRST_GROUP_ID,SECOND_GROUP_ID)
       GROUP BY userID
    ) AS T1;

    Full (console) output:

  • That display is the output of the sql command console. That's also the reason for those fancy ascii table art. ;) I like to interact with my database simply by using the mysql command via a login ssh shell.

    If you really just want one number this might be what you want:

    SQL
    SELECT COUNT(userID) AS number
    FROM
    (
       SELECT
       userID, COUNT(groupID)
       FROM wcfN_user_to_group
       WHERE groupID IN (FIRST_GROUP_ID,SECOND_GROUP_ID)
       GROUP BY userID
    ) AS T1;

    Full (console) output:

    Okay, thats where we are getting our wires crossed lol.

    I am not trying to look up the number, I am trying to display the number, on my site for public view.

    So its a Gaming Community, and we play various games, so I want on each games page to have the numbers displayed, how many people for instance play World of Warcraft, and how many are PvPers, PvErs, Crafters, Ect, with WOW and PVP, being 2 examples of ranks.

    I am trying to make something like this,

  • Well, yes the query is the basic. You'll probably need it anyway. I'm not firm in php. Basicly you'll need to embed the query (SQL part) in an php script which will generate the nice html output.

  • So it seems that WCF already does this, with 1 group anyway, anyone know how to do this for any group?

    Code
    <h2 class="sectionTitle" id="group{@$team->groupID}">{$team->groupName|language} <span class="badge">{#$team->getMembers()|count}</span>

    To for Instance give me a readout for even 1 group? Lets say, do that for Group 34?

  • Bumping because im still lost on this.

    All the examples I am finding on the web, trying to figure this out, want to print all kinds of stuff, and I just want a number, and can not figure out how to do either. They are over complicating it, trying to teach me how to to print the entire database lol.

    I was even going to settle for just grabbing the numbers by hand and manually updating them all the time. However, the number of users in a group are not even listed anywhere on the front end at all. My users want to know how many people are in these groups.


    I did notice, the ACP does this in the user groups list. Gives a row, with the number of users in it with jsUSERGROUPLIST but I cant find that JS, even that would work, If I could just pull the data from the acp to the front end.

    If someone knows a way to copy the TD for the number of users, and place that elsewhere that would even work wonderfully.

    3 Mal editiert, zuletzt von cyberlocc (4. Februar 2019 um 09:05)

Jetzt mitmachen!

Sie haben noch kein Benutzerkonto auf unserer Seite? Registrieren Sie sich kostenlos und nehmen Sie an unserer Community teil!