MySQL count complex query results?

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

SELECT COUNT(*) should work with the query. What's the error message that you are getting? — David Z.
– David Z., Commented Apr 19, 2012 at 1:51
Like SuperNoob suggests, I find it easier to return the num_rows than to use the mysql COUNT — JT Smith
– JT Smith, Commented Apr 19, 2012 at 2:08
COUNT(*) is probably more efficient than mysql_num_rows() since the result set has to be return from the database for mysql_num_rows() to work. — Jeff
– Jeff, Commented Apr 19, 2012 at 2:40
COUNT won't work. I get an array with with increment keys with values of 1 in each. [1] => 1 [2] => 1 and so on. — Sweepster
– Sweepster, Commented Apr 19, 2012 at 2:47

Ezequiel Muns · Accepted Answer · 2012-04-24 05:29:03Z

3

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

edited Apr 24, 2012 at 5:29

answered Apr 19, 2012 at 3:55

Ezequiel Muns

7,78035 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ezequiel Muns Over a year ago

You may also be able to just run it as the inner query with the COUNT(*) as count replacing the *, but I don't remember how this interacts with the HAVING clause.

Sweepster Over a year ago

This doesn't work. No results are found. See updated question with layout of taxi and post tables. Assuming $user is 1, the query should find postID 1,3,4,5 thus count should be 4. Your solution results in count being 0.

Ezequiel Muns Over a year ago

I have fixed and edited the SQL by loading the data into a test database and running it. The main difference is that the test for userID has moved to the ON clause of the LEFT JOIN, and the test for value IS NULL has been removed (I recommend a NOT NULL constraint on this column). You'll find that the correct answer for $user = 1 is actually 3, since they have made values for postIDs 1 and 2.

Sweepster Over a year ago

yes but I am looking for all posted that have value 1 AND all posts that don't have any values at all. Your query is finding all null values only. This means I'm missing an "AND value = 1" somewhere correct?

Ezequiel Muns Over a year ago

Yes, you're right. Mind this wasn't communicated all that well in your description. I've updated the answer to get this in.

Rohan Prabhu · Accepted Answer · 2012-04-19 01:53:05Z

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

Teja · Accepted Answer · 2012-04-19 01:51:27Z

0

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

answered Apr 19, 2012 at 1:51

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Comments

kjones · Accepted Answer · 2012-04-19 01:52:53Z

0

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?

answered Apr 19, 2012 at 1:52

kjones

1,4531 gold badge15 silver badges30 bronze badges

5 Comments

Sweepster Over a year ago

I get an array. I need a numeric value.

kjones Over a year ago

You should be able to initialize a variable with the integer value of your count directly from the array that is returned.

kjones Over a year ago

So COUNT(*) is working correctly, and you have a new question. You should remove the part where you state that COUNT(*) is not working.

Sweepster Over a year ago

Duely noted. I suppose the better question is, how do I check if $count is greater than 10?

ypercubeᵀᴹ Over a year ago

@kjones: +1 this is correct. Perhaps you can add an alias, eg.: SELECT COUNT(*) AS cnt FROM ... so it's easier for the OP to retrieve the cnt in the PHP code.

Mosty Mostacho · Accepted Answer · 2012-04-19 01:53:02Z

0

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.

answered Apr 19, 2012 at 1:53

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

2 Comments

Sweepster Over a year ago

you're right, if forgot to remove LIMIT. However, COUNT doesn't work.

Mosty Mostacho Over a year ago

@Jonathan Yes, count works perfectly. You're messing things up with your PHP code, which you haven't provided.

Sweepster · Accepted Answer · 2012-04-19 03:39:52Z

0

I found the answer and it was dead simple:

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

answered Apr 19, 2012 at 3:39

Sweepster

1,9594 gold badges30 silver badges79 bronze badges

2 Comments

Ezequiel Muns Over a year ago

This is indeed a very inefficient way of doing it, yes. Just cause it works doesn't mean it's right.

Sweepster Over a year ago

I agree it is, but without a solution to my query I have no alternative.

Collectives™ on Stack Overflow

MySQL count complex query results?

6 Answers 6

5 Comments

Comments

Comments

5 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

Comments

Comments

5 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related