php - Combine two mysql COUNT(*) results, different columns

Answer

Solution:

The most straight forward way is to use SUM / Condition

SELECT
     CASE WHEN sender = '$userid' THEN recipient else sender END AS OtherUser,
     SUM(sender = '$userid') AS RCount,
     SUM(recipient = '$userid') AS SCount,
     COUNT(*) total
FROM
     privatemessages 
WHERE
     '$userid' IN (sender , recipient )
GROUP BY
     OtherUser
Copy code

DEMO

Update

I cleaned up the SUM and GROUP BY statements thanks to deltab and Michael Buen

Answer

Solution:

The most straightforward way is just to use 2 queries.
There is absolutely no harm in requesting 2 different sets of data using 2 queries yet not a single reason to do it with one.

Answer

Solution:

You can combine them with UNION like this:

SELECT otheruser, COUNT(*) FROM
(SELECT recipient AS otheruser
FROM privatemessages 
WHERE sender = '$userid' 
GROUP BY recipient
UNION
SELECT sender AS otheruser
FROM privatemessages 
WHERE recipient = '$userid' 
GROUP BY sender)
Copy code

Answer

Solution:

SELECT recipient AS r, sender AS s, COUNT(total) // can't use same alias
FROM privatemessages
WHERE sender = '$userid' AND recipient = '$userid'
GROUP BY s;
Copy code

Then in your results function, call the count

while ($row = $result) {
  echo $row['COUNT(total)'];
}
Copy code

I believe this query should work as expected. The only issue with your original was the dual alias and your grouping of two different fields. This is possible in 2 seperate queries but combined, it can't be done (at least the alias, don't think so with GROUP BY).

Hope this works for you.

UPDATE

A great alternative toCOUNT is to simply usenum_rows... Therefore, the query above would be:

SELECT recipient AS r, sender AS s // can't use same alias
FROM privatemessages
WHERE s = '$userid' AND r = '$userid'
GROUP BY s;
Copy code

And then in your results function, callnum_rows function:

mysql_num_rows($result);
Copy code