CODEWITHSUNDEEP
phpmysqlpdocount
Marty
Marty

Reputation: 13

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

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!

Upvotes: 1

Views: 1066

Answers (4)

Marc B
Marc B

Reputation: 360602

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.

Upvotes: 2

Martin
Martin

Reputation: 6687

Yes it's reliable in this use case!

Upvotes: 0

Mickle Foretic
Mickle Foretic

Reputation: 1409

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.

Upvotes: 0

Hanky Panky
Hanky Panky

Reputation: 46900

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

Upvotes: 0

Related Questions