MySQL计算复杂的查询结果？

Question

I have the following query:

$count = (SELECT COUNT(*) FROM post GROUP BY ID
HAVING ID NOT IN (SELECT taxiID FROM taxi WHERE userID = '.$userID.' AND value IS NOT NULL)
ORDER BY postID), OBJECT);

Count contains this:

count = Array ( [0] => stdClass Object ( [COUNT(*)] => 1 ) [1] => stdClass Object ( [COUNT(*)] => 1 ) [2] => stdClass Object ( [COUNT(*)] => 1 ) [3] => stdClass Object ( [COUNT(*)] => 1 ) [4] => stdClass Object ( [COUNT(*)] => 1 ) [5] => stdClass Object ( [COUNT(*)] => 1 ) [6] => stdClass Object ( [COUNT(*)] => 1 ) [7] => stdClass Object ( [COUNT(*)] => 1 ) [8] => stdClass Object ( [COUNT(*)] => 1 ) [9] => stdClass Object ( [COUNT(*)] => 1 ) [10] => stdClass Object ( [COUNT(*)] => 1 ) [11] => stdClass Object ( [COUNT(*)] => 1 ) [12] => stdClass Object ( [COUNT(*)] => 1 ) [13] => stdClass Object ( [COUNT(*)] => 1 ) [14] => stdClass Object ( [COUNT(*)] => 1 ) [15] => stdClass Object ( [COUNT(*)] => 1 ) [16] => stdClass Object ( [COUNT(*)] => 1 ) [17] => stdClass Object ( [COUNT(*)] => 1 ) [18] => stdClass Object ( [COUNT(*)] => 1 ) [19] => stdClass Object ( [COUNT(*)] => 1 )

I need to count the number of results delivered by the above. Thing is, I have no idea how to use the result!

I had this code but now it won't work:

<?php if($count[0]->{'COUNT(*)'} > 10){ ?
    echo "Load More";
}else { 
    echo "Nothing to load";
} ?>

$count should be more than 10 and my php should echo Load More but it is echoing Nothing to load.

The taxi table looks like this:

ID    taxiID    userID    value
1     1         1         1
2     1         6         1
3     1         4         0
4     2         1         0
5     2         6         1
6     2         4         0
7     3         6         1
8     3         4         0

The post table looks like this:

ID    postID    randomNum
1     1         564
2     2         789
3     3         234
4     4         845
5     5         089

Assuming $userID is 1, the query returns postID 1,3,4,5 (1 is liked, 3 is not liked and not disliked by user 1, 4 and 5 are not liked and not disliked by any user). Therefore $count should contain 4 (4 results are found).

If my query is inefficient, how do I adapt it to be efficient?

Ultimately, the question is how do I do something like:

if ($count > 10) {}

Answer 1

Your problem is, your query isn't returning what you think it returns (it always helps to run you query standalone, to see if the result set is what you expect).

Right, let's break this down.

It is counting all posts that the user has not liked or disliked. likes and dislikes are stored in the taxi table. taxi.taxiID matches post.ID. Hence if the userID with any value that isn't null is found, ignore that post.ID. I am counting those post.ID which are not ignored

You're trying count all posts that don't have a matching record in the taxi table, for that userID. What you want here is to JOIN the tables and get those rows in post that would normally be excluded by the join. This is achieved by an left outer join

(edited)

SELECT p.ID, t.taxiID
FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
HAVING t.taxiID IS NULL

That HAVING clause is saying: only those rows in the resultset that didn't have a corresponding t.taxiID.

If you run this query and get the expected set of rows (posts that do not have likes or dislikes by that user) THEN you can add an outer query to count the number of returned rows:

SELECT COUNT(*) as count FROM (
    SELECT p.ID, t.taxiID
    FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
    HAVING t.taxiID IS NULL
) a

This should return a single scalar named count. In this case you'll be able to say:

if ($count[0]->count > 10) { blah blah blah }

(2nd edit) This inner query will get you those posts that have either value = 1 in the taxi table, or no value at all, which results in 4 being returned for your example:

SELECT p.ID, t.taxiID, t.value
FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
HAVING t.taxiID IS NULL OR t.value = 1