Freddy Freddy - 7 months ago 10
PHP Question

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

I have a table called

private_messages
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
Alice
is in
message_from
and in
message_to
.

Assume my
private_messages
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
1
. Therefore when I echo, let's say
$active_conversations
, the number
2
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
select count(id) from private_messages group by message_from,message_to

then you get appropriate result