Freddy Freddy - 1 year ago 36
PHP Question

Checking multiple rows in table and counting them as 1 if rows are matching

I have a table called

with the following structure:

id message_from message_to

I am trying to determine the number of active chat rooms, but the table stores all messages sent and received, therefore there may be many rows where
is in
and in

Assume my
table has 4 rows:

id message_from message_to
1 Alice conor
2 Alice conor
3 connor Alice
4 Anderson conor

The conversation between Alice and conor should be considered as
. Therefore when I echo, let's say
, the number
should be printed.

I am assuming this involved an array? i.e.
$conversations = [];
. But I do not know or understand what to do beyond that.

Answer Source
select count(id) from private_messages group by message_from,message_to

then you get appropriate result