Using count() to count results from PDO in PHP?

Question

I'm in the process of converting an old mysql_X site to work with PDO, and so far, so good (I think). I have a question regarding the reliability of counting the results of queries, however.

Currently, I've got everything going like this (I've removed the try / catch error code to make this easier to read):

$stm = $db->prepare("SELECT COUNT(*) FROM table WHERE somevar = '1'");
$stm->execute();
$count = $stm->fetchColumn();

if ($count > 0){
    $stm = $db->prepare("SELECT * FROM table WHERE somevar = '1'");
    $stm->execute();
    $result = $stm->fetchAll();
}

There might be stupid problems with doing it this way, and I invite you to tell me if there are, but my question is really about cutting down on database queries. I've noticed that if I cut the first statement out, run the second by itself, and then use PHP's count() to count the results, I still seem to get a reliable row count, with only one query, like this:

$stm = $db->prepare("SELECT * FROM table WHERE somevar = '1'");
$stm->execute();
$result = $stm->fetchAll();

$count = count($result);
if ($count > 0){
    //do whatever
}

Are there any pitfalls to doing it this way instead? Is it reliable? And are there any glaring, stupid mistakes in my PDO here? Thanks for the help!

I have not tried num_rows. I've tried rowCount, which also works, but I've read that it is not advisable to use that with SELECT queries, as the results might not always been reliable. — Marty
– Marty, Commented Jan 18, 2013 at 18:26

Marc B · Accepted Answer · 2013-01-18 18:25:23Z

2

Doing the count in MySQL is preferable, especially if the count value is the only result you're interested in. Compare your versions to equivalent question "how many chocolate bars does the grocery store have in stock?"

1) count in the db: SELECT count(*) .... Drive to the store, count the chocolate bars, write down the number, drive home, read the number off your slip of paper

2) count in PHP: SELECT * .... Drive to the store. Buy all the chocolate bars. Truck them home. Count them on your living room floor. Write the results on a piece of paper. Throw away the chocolate bars. Read number off the paper.

which one is more efficient/less costly? Not a big deal if your db/table only has a few records. When you start reaching the thousands/millions of records, version 2) is absolutely ludicrious and likely to burn through your bandwidth, blow up your PHP memory limit, and drive your CPU usage into the stratosphere.

That being said, there's no point in running two queries, one to just count how many records you MAY get. Such a system is vulnerable to race conditions. e.g. you do your count and get (say) 1 record. by the time you go to run the second query and fetch that record, some OTHER parallel process has gone and inserted another record, or deleted the one you'd wanted.

answered Jan 18, 2013 at 18:25

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

2 Comments

Marty Over a year ago

I figured there would be a point at which it would make more sense to do the count in MySQL. The table I'm working with in this case only has 13 entries now, and will not grow to be very big, if ever, for a long time. In this case, it's also useful to me to grab all the results at once - I'd be grabbing them all anyway, so it's not a huge detriment to the server either way.

Marty Over a year ago

This is a good observation though, and I think I'll have to keep it in mind, since I've got another table in the same database that has many more entries, and which I will have to limit the use of more stringently. Thanks!

Hanky Panky · Accepted Answer · 2013-01-18 18:13:08Z

0

In first case you are counting using MYSQL, and in second case you are counting using PHP. Both are essentialy same results.

answered Jan 18, 2013 at 18:13

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

3 Comments

Marty Over a year ago

Yes, I realize this. And maybe there is a speed trade-off there that I'm not taking into the picture... but for now, I'm mostly interested in making sure I'm getting reliable results and limiting the number of database queries when I can.

Hanky Panky Over a year ago

@Marty If you just have to display a count then MYSQL way is best in terms of speed because you dont have to then grab all the rows to know the count. But if you have to display them all then PHP way is better because then you are not running 2 queries

Marty Over a year ago

I do have to count and then display the results in this case, so I think it might be preferable to use PHP's count here. Thanks for the feedback!

Mickle Foretic · Accepted Answer · 2013-01-18 18:13:22Z

0

Your usage of the queries is correct. The only problem will appear when you use LIMIT, because the COUNT(*) and the count($result) will be different.

COUNT(*) will count all the rows that the query would have returned (given that the counting query is the same and not using LIMIT)

count($result) will count just the returned rows, so if you use LIMIT, you will just get the results up to the given limit.

answered Jan 18, 2013 at 18:13

Mickle Foretic

1,41914 silver badges24 bronze badges

2 Comments

Hanky Panky Over a year ago

If you use LIMIT on both queries, result will still be same.

Mickle Foretic Over a year ago

That is why I said: "given that the counting query is the same and not using LIMIT"

Martin · Accepted Answer · 2013-01-18 18:14:05Z

0

Yes it's reliable in this use case!

answered Jan 18, 2013 at 18:14

Martin

6,7054 gold badges28 silver badges28 bronze badges

Collectives™ on Stack Overflow

Using count() to count results from PDO in PHP?

4 Answers 4

2 Comments

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related