SQL count limiting results

Question

I'm having an issue with following query:

SELECT 
   badge.name AS badge_name, badge.description, badge.type, badges.time, user.name AS user_name 
FROM 
   badges LEFT JOIN badge ON badges.badge_name = badge.name LEFT JOIN user ON user.id=badges.user_id 
WHERE 
   user.name IS NOT NULL 
ORDER BY badges.time DESC 
LIMIT 5

Now, I'd like to check that the amount of results is not 0, so I check like I always do with by adding this after the SELECT: count(1) AS counter. However, this influences the results.

How it should be.

How it is with the count.

I've seen that this might be an issue due to also having a LIMIT, but what's the most efficient way to circumvent this? I just want to check whether there are any results returned or not, to display a proper message it there are no results. I'm using PDO, but since it's a SELECT i can't use the ->rowCount() to check the amount of rows returned.

EDIT:

I want to determine whether there are any results, yes or no. My normal way of doing so is using count(1) AS counter, and checking the value of the counter as follows:

while($row['counter'] = $STH->fetch()){
    if($row['counter'] == 0){
        // Error message
    }else{
        echo $row['badge_name'] . "etc...";
    }
}

However, this seems to mess up the results due to the LIMIT (check the SQLFiddles).

So: how can I check this, preferably in a single query?

I suggest that you explain what you are trying to do. Based on what I can understand is that you want to get the resultset, but at the same time you want some way to determine if no rows were returned. If it's returns a collection, I would have guessed that you can certainly interrogate the count, but I'm not a PHP guy. I recommend that you rewrite your requirement so people can better understand. — Robert Co, Aug 24 '13 at 15:55

Vahid Hallaji · Answer 1 · 2013-08-24T15:58:43.777

1

Add count(*) as count to count number of rows.

SELECT
   count(*) as `count`
FROM
   badges LEFT JOIN badge ON badges.badge_name = badge.name LEFT JOIN user ON user.id=badges.user_id
WHERE
   user.name IS NOT NULL

edited Aug 24 '13 at 15:58

answered Aug 24 '13 at 15:47

Vahid Hallaji

7,159
5
42
51

This seems to [also return one result](http://sqlfiddle.com/#!2/7ae77/16) whilst there are [two expected](http://sqlfiddle.com/#!2/7ae77/5) – jdepypere Aug 24 '13 at 15:50
@arbitter Do you want to know how many records you have on table by query? – Vahid Hallaji Aug 24 '13 at 15:55
I just want to check whether there is a result or not. In normal situations, the table should be saturated and 5 records should be a minimum. But in case something happens and it suddenly drops to 0, I'd like to say that there are no results instead of having just nothing. – jdepypere Aug 24 '13 at 15:57
1

I updated the answer. It return number of rows as `count` if exist any. – Vahid Hallaji Aug 24 '13 at 16:01
Okay I've made myself a bit unclear again - I'd also want to get the results of the table as well, not only the count. Is it possible to do this in a single query? – jdepypere Aug 24 '13 at 16:03
Why not? Adding a second query seems a bit unnecessary since that would require double the resources. Reading into other questions like [this one](http://stackoverflow.com/questions/1125966/sql-count-total-number-of-rows-whilst-using-limit), it seems that using `SELECT FOUND_ROWS();` is also a valid option, without the need of a second query. – jdepypere Aug 24 '13 at 16:14
@arbitter I think It's not good idea to add count of all records as a `field` in results. Run your sql as yourself `whihout count(*)` to get data. Also It will be clean to get number of record by PDO `rowCount()` method – Vahid Hallaji Aug 24 '13 at 16:14

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

As I'm not personally familiar with MySQL features covering this I would normally make it a comment on your question but I don't have the required rep yet. So, I have to make it an "answer".

It sounds like you want the equivalent of T-SQL's @@ROWCOUNT. From what I can find that's talked about here

Does Mysql have an equivalent to @@ROWCOUNT like in mssql?

and here

obtain row count in mysql query itself

Maybe some of that is of use to you.

edited May 23 '17 at 12:21

Community

1
1

answered Aug 24 '13 at 16:17

WillG

834
1
10
15

Your first link indeed links to a correct solution, using `SELECT ROW_COUNT();` in a new query is more efficient than doing a whole query and counting the results. – jdepypere Aug 24 '13 at 16:23

SQL count limiting results

2 Answers2