How can I count selected rows in MySQL?

Question

Here is my query:

SELECT 
    a.*
FROM
    qanda q
        JOIN
    qanda a ON q.Id = a.related
WHERE
    a.type = 1
AND a.author_id = 29
AND q.amount IS NULL;

Now, it selects 3 rows in reality. Ok I want it returns 3 instead of a.*. Actually I don't need any valye of those columns, I just need to count the number of selected rows. How can I do that?

Note: I can use my current query and then count the number of rows by PHP. But I don't like that.

The answer below is probably your best solution. Your driver also should have a function for this, http://php.net/manual/en/pdostatement.rowcount.php, http://php.net/manual/en/mysqli-result.num-rows.php — chris85, Sep 09 '16 at 01:05
@chris85 Even if the driver supports it, you still have to have MySQL query and count the number of rows. — grepsedawk, Sep 09 '16 at 01:10
@Pachonk ...yea that's why the comment was prefaced with `The answer below is probably your best solution.` — chris85, Sep 09 '16 at 01:12
Completely understood, I just wanted to make it clear that even though it's option is available, it would be less efficient. I didn't mean to come off mean. — grepsedawk, Sep 09 '16 at 01:13

score 6 · Accepted Answer · answered Sep 09 '16 at 00:54

6

Simple. Use COUNT() in MySQL

SELECT 
    COUNT(a.id) as count
FROM
    qanda q
        JOIN
    qanda a ON q.Id = a.related
WHERE
    a.type = 1
AND a.author_id = 29
AND q.amount IS NULL;

answered Sep 09 '16 at 00:54

grepsedawk

3,324
2
26
49

Just one thing, Why not `COUNT(1)` instead of `COUNT(a.id)` ? – Martin AJ Sep 09 '16 at 01:30
1

No reason. Personal preference. – grepsedawk Sep 09 '16 at 01:31
1

See http://stackoverflow.com/questions/5179969/what-is-better-in-mysql-count-or-count1 for more details. – grepsedawk Sep 09 '16 at 01:33

How can I count selected rows in MySQL?

1 Answers1