CODEWITHSUNDEEP
phpsqldatabaseunion
Kaizokupuffball
Kaizokupuffball

Reputation: 2843

SQL Union only counting one query

Im trying to count rows in a table. Currently im using this:

$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";

As you see, user1 can be both $user1 AND $user2. but it does not count the rows after the UNION, it only does the first query before UNION so im ending up with 0 when i actually should be counting 1 row.

How do I solve this?

Upvotes: 1

Views: 153

Answers (4)

Zane Bien
Zane Bien

Reputation: 23135

You can use an even simpler solution:

SELECT COUNT(*)
FROM friends
WHERE 
    $user1 IN (user1,user2) AND 
    $user2 IN (user1,user2) AND
    valid = 0

Upvotes: 1

Basic
Basic

Reputation: 26766

Your query is actually returning 2 records - 0 for the first count and 1 for the second. You could use a sub-query to SUM() your results... 

$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
)";

Upvotes: 2

Chandu
Chandu

Reputation: 82933

Another alternative, replace your query with this:

$sql = "SELECT COUNT(*) 
    FROM `friends` 
    WHERE (
            (`user1`='".$user1."' AND `user2`='".$user2."') OR
            (`user1`='".$user2."' AND `user2`='".$user1."')
          )
     AND `valid`=0";

Upvotes: 4

Daniel Li
Daniel Li

Reputation: 15389

Use UNION ALL instead. UNION will exclude duplicate rows.

Upvotes: 1

Related Questions