在sql查询中检查两个单独的东西

Question

I am trying to create a friend relationship query. I want the following query to do two things, rather than making another very similar query.

I am wanting to check for pending requests and sent requests. Pending requests are when the user is friend_two. Sent requests are when friend_one is the user_id.

So, is there anyway I can adjust my where clause to something like this (I do not know how to write this out in SQL) -

WHERE friend_one = ? As pending_user
WHERE friend_two = ? AS sent_user

I am not sure if AScan be added to a where clause. The thing about the code I just wrote above is that the placeholder value will be the same value and I am not sure how I could write that in the php.

Right now, if I have the same user in the friend_one and friend_two column it counts all of the occurences of the user's occurence ie: if user #2 has his id in the friend_one column only 2 times, but has his id comes up in the friend_two column 8 times, the query will count 10 because of m,y where clause. I need the values to be 2 and 8.

How can I do this? See code and table below.

SELECT *, COUNT(friend_one) AS pending_count , COUNT(friend_two) AS requests_sent   
FROM friends
WHERE friend_one OR friend_two = ?
AND status = ?

friends table

Table
Create Table
friends
CREATE TABLE `friends` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `friend_one` int(11) NOT NULL DEFAULT '0',
 `friend_two` int(11) NOT NULL DEFAULT '0',
 `status` enum('0','1','2') COLLATE utf8_unicode_ci DEFAULT '0',
 `date` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `friend_two` (`friend_two`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Square = requests_sent

Circle = pending_count

Answer 1

with @name, @status parameters. I belive you want something like this.

SELECT COUNT(CASE WHEN `friend_one` = @name THEN 1 END) as `pending_count`,
       COUNT(CASE WHEN `friend_two` = @name THEN 1 END) as `requests_count`
FROM friends
WHERE @name IN ( `friend_one` , `friend_two` )
  AND `status` = @status