CODEWITHSUNDEEP
phpmysqlgrouping
joe92
joe92

Reputation: 643

How to group results from MySQL

MySQL is my weak point. I have the following MySQL query on a page of mine. The variables $this_groupMembers and $this_groupInvites are both predetermined strings in the form '1,2,3' etc.:

SELECT
    username
FROM
    chat_users
WHERE
    ID IN($this_groupMembers) OR
    ID IN($this_groupInvites)

What I need to be able to do is to group the results so that I can access the existing members separately from the invited members. I.e. Create the variables $groupMembers_usernames and $groupInvites_usernames from the results of the MySQL query without having to dissect the results with PHP.

Is there a way to do this so that MySQL does all the work?

Thanks you,
Joe

Upvotes: 0

Views: 95

Answers (2)

joao_dv
joao_dv

Reputation: 1213

You can use a UNION with constants to classify the returned rows:

SELECT
    username, 'member'
FROM
    chat_users
WHERE
    ID IN($this_groupMembers)
UNION
SELECT
    username, 'invitee'
FROM
    chat_users
WHERE
    ID IN($this_groupInvites)

Upvotes: 3

nico
nico

Reputation: 51640

The easiest thing would be to have two separate queries, however, if -for whatever reason- you really need one you can:

SELECT
    username, ID IN($this_groupMembers) AS member
FROM
    chat_users
WHERE
    ID IN($this_groupMembers) OR
    ID IN($this_groupInvites)

member will be 1 if member, 0 if invited

Upvotes: 0

Related Questions