我正在尝试计算表中的行数。目前我正在使用这个:
$sql = "SELECT COUNT(*)
FROM `friends`
WHERE `user1`='".$user1."'
AND `user2`='".$user2."'
AND `valid`=0
UNION
SELECT COUNT(*)
FROM `friends`
WHERE `user1`='".$user2."'
AND `user2`='".$user1."'
AND `valid`=0";
正如您所看到的,user1
可以是$user1
和$user2
。但它不计算UNION
之后的行,它只计算UNION
之前的第一个查询,所以当我实际上应该计算1行时,我以0结束。
我该如何解决此问题?
另一种选择,将您的查询替换为:
$sql = "SELECT COUNT(*)
FROM `friends`
WHERE (
(`user1`='".$user1."' AND `user2`='".$user2."') OR
(`user1`='".$user2."' AND `user2`='".$user1."')
)
AND `valid`=0";
您的查询实际上返回了两条记录——第一条计数为0
,第二条计数为1
。您可以使用子查询SUM()
您的结果。。。
$sql = "SELECT SUM(A) AS COUNT FROM
(SELECT COUNT(*) AS A
FROM `friends`
WHERE `user1`='".$user1."'
AND `user2`='".$user2."'
AND `valid`=0
UNION
SELECT COUNT(*)
FROM `friends`
WHERE `user1`='".$user2."'
AND `user2`='".$user1."'
AND `valid`=0
)";
请改用UNION ALL
。UNION
将排除重复的行。
您可以使用更简单的解决方案:
SELECT COUNT(*)
FROM friends
WHERE
$user1 IN (user1,user2) AND
$user2 IN (user1,user2) AND
valid = 0