CODEWITHSUNDEEP
phpmysqlcount
Sweepster
Sweepster

Reputation: 1949

MySQL count complex query results?

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) {}

Upvotes: 2

Views: 2378

Answers (6)

Ezequiel Muns
Ezequiel Muns

Reputation: 7742

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

Upvotes: 3

Sweepster
Sweepster

Reputation: 1949

I found the answer and it was dead simple:

if (count($count) > 10) {}

Upvotes: 0

Rohan Prabhu
Rohan Prabhu

Reputation: 7302

In case you want to know how many results would have been returned WITHOUT the LIMIT clause, according to the MySQL documentation:

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Why not this?

SELECT COUNT(*) FROM post
WHERE postID NOT IN (
  SELECT taxiID FROM taxi 
  WHERE userID = '.$userID.' AND value = 0
)
LIMIT 10

Note there is no need to perform an order by if you are only looking for the count. Also note you have a limit there so the result won't have more than 10 records. Not sure if that is the idea.

Upvotes: 0

kjones
kjones

Reputation: 1423

SELECT COUNT(*) FROM post
WHERE postID NOT IN                     
  ( SELECT taxiID
    FROM taxi 
    WHERE userID = '.$userID.'
      AND value = 0
  )

Can you also provide us with the error message if this does not work?

Upvotes: 0

Teja
Teja

Reputation: 13534

SELECT COUNT(*) FROM post
WHERE postID NOT IN                     
  ( SELECT taxiID
    FROM taxi 
    WHERE userID = '.$userID.'
      AND value = 0
  ) 
ORDER BY postID;

Upvotes: 0

Related Questions