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, php.net/manual/en/pdostatement.rowcount.php, php.net/manual/en/mysqli-result.num-rows.php — chris85
– chris85, Commented Sep 9, 2016 at 1:05
@chris85 Even if the driver supports it, you still have to have MySQL query and count the number of rows. — grepsedawk
– grepsedawk, Commented Sep 9, 2016 at 1:10
@Pachonk ...yea that's why the comment was prefaced with The answer below is probably your best solution. — chris85
– chris85, Commented Sep 9, 2016 at 1: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
– grepsedawk, Commented Sep 9, 2016 at 1:13

grepsedawk · Accepted Answer · 2016-09-09 00:54:48Z

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 9, 2016 at 0:54

grepsedawk

3,4142 gold badges28 silver badges49 bronze badges

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

3 Comments

Martin AJ Over a year ago

Just one thing, Why not COUNT(1) instead of COUNT(a.id) ?

grepsedawk Over a year ago

No reason. Personal preference.

grepsedawk Over a year ago

See stackoverflow.com/questions/5179969/… for more details.

Collectives™ on Stack Overflow

How can I count selected rows in MySQL?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related