Parent and child messages in the same MYSQL query

Question

From the above table I wanted to extract a list with all messages where parent_id = 0 and sender_id or receiver_id is equal to my login id.

Table:Messages
id | parent_id | sender_id | receiver_id | subject | message | read

This was easy but I could not figure out how to count all child messages for messages WHERE parent_id = 0.

I tried this:

$my_messages = mysql_query("
  SELECT
       
messages.id, messages.sender_id, messages.receiver_id, messages.subject, messages.message, messages.minute,
 messages.hour, messages.day, messages.month, messages.year, COUNT(*) as 'mcount'
    FROM messages 
    LEFT JOIN messages AS mchild ON mchild.parent_id = messages.id 
    WHERE(messages.sender_id='$login_session' or messages.receiver_id='$login_session') 
    and messages.parent_id = '0'
    ORDER BY messages.year DESC, messages.month DESC, messages.day DESC, messages.hour DESC, messages.minute DESC
   
");

An additional thing i could not figure was how do I count all child+parent messages for each parent_id=0 WHERE read=1 if sender_id = $login_id or read=1 if receiver_id = $login_id

Here is an example:

Table:Messages
id | parent_id | sender_id | receiver_id | subject | message | read
 1 |         0 | Paul      | John        | Test    | Test    | 0
 2 |         0 | Paul      | Chris       | Test    | Test    | 0
 3 |         1 | john      | Paul        | Test    | Test    | 0
 4 |         1 | Paul      | John        | Test    | Test    | 1
 5 |         1 | John      | John        | Test    | Test    | 0
 6 |         0 | Paul      | Jack        | Test    | Test    | 0

Output:

ID:1 - 4 messages (1 parent+ 3 children), $unread=1 because read = 1 for  ID:4 which is a child for ID:1
ID:2 - 1 message
ID:6 - 1 message

Answer 1

Use a self join:

SELECT m1.id,
       COUNT(*) AS childCount
FROM messages m1
INNER JOIN
messages m2
    IN m1.id = m2.parent_id
WHERE '$login_session' IN(m1.sender_id,m1.receiver_id)
  AND m1.id = 0 -- you can remove this condition if you want all parents

GROUP BY m1.id

Typically the COUNT function is used with GROUP BY, the groups in this case being each parent. But since you only want parent_id of zero, I added this to the WHERE clause, which will filter out every group but this one. You may remove it if you want more information.