PDO count rows from select statement

Hi guys,

I'm migrating my site from mysql_* functions to PDO.
On PHP.net, it says that PDO::exec does not return results from a select statement.
I have just one question: How can I count the rows returned by a select statement?
It doen't even need to be using exec.

Thanks,
-Tony

 

PDO:exec is used for update/delete statements (and is some rare instances a SELECT statement where you do not expect anything returned).

For selects, use query:

<?php
$sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
$row = $conn->query($sql);
var_dump($row);

PHP:

Example taken from: http://php.net/manual/en/pdo.query.php

 

When using query($sql), how do I retrieve the number of rows selected?

Thanks,
-Tony

 

How so?
Example, please?

Thanks,
-Tony

 

The 'proper' way should be PDOStatement::rowCount, but not all SQL engines return a row count for SELECT in the first place. That's why it's not included in the PDO result set, as PDO is multi-engine so it can't rely on things that are mySQL specific actually working.

The best way is to not design so you need that number before you process the output -- failing that, run a COUNT instead of a SELECT with the same WHERE values first... as suggested here:

http://php.net/manual/en/pdostatement.rowcount.php
See Example 2.

You may also consider PDOStatement::fetchAll to dump the results to a flat array and then calling count -- but be warned that uses more memory and can be slower as a result... it can also be faster as it transfers the entire result set to PHP in one fell swoop instead of using multiple separate SQL function requests over time... it's a craps shoot depending on environment and the query in question.

 

SO rowCount will work on MySQL.
Is that faster than running COUNT before SELECT?

Thanks,
-Tony

 

That depends. Count on an indexed field is pretty fast, almost instantaneous.

 

COUNT(*) is optimized by MySQL as opposed to doing count(primary field), count(index), and it is performed from the table descriptor without actually counting the records.

MySQL PDO driver does not support rowCount for selects. I would advise against using rowCount on a select as stated on the manual for rowcount:

I would suggest to do what is told above for normal queries. If you have a complex query or a query that takes seconds to finish, I would suggest looking into MySQL's CALC FOUND ROWS function to avoid doing the query twice.

 

Some people know certain databases better than other people. I learned something today. Thanks.

 

But only when you aren't reducing the result set with WHERE...

COUNT on a single field vs. * is effectively identical once you have WHERE involved... for obvious reasons, you can't pull the result count of WHERE from the table count.

 

When you do count(*) MySQL will only check the number of rows matching the WHERE. As opposed to count(field) where MySQL will check that every field to be != NULL on all rows matching the where. That reduces efficiency greatly on large tables.

Here is a great blog post by Mr.Schwartz from MySQL Performance Blog (I really find their posts to be informative in regards to optimization):

http://www.mysqlperformanceblog.com/2008/09/20/a-common-problem-when-optimizing-count/

I too was a skeptic in this regards until I did my own benchmark testing on a db table with primary/indexed and non index field. Doing an explain while you are doing the testing (if you are going to do the testing) will actually help you understand how the count() function works.

 

Makes sense:

++count;

is going to be faster than

if (field != null) ++count;

Though the performance difference should only be 1% -- since it should just be one conditional extra -- but given we're talking using one character - an asterisk -- instead of many, choosing to use (*) when pulling nothing but a row could should be a no-brainer. Hell, the smaller query not having to be sent over the network alone (since SQL transfers are network connections) could be all it takes; cross that packet size boundary and the impact is quite noticeable.

But then some people will still choose the other way 'because' -- see single quotes vs. double -- you'd think hitting one less key (shift) and the 1-5% speed difference on parsing would make it a no-brainer... yet you still see most PHP developers jumping though hoops over the simplest of things like strings. Query strings end up no different in that way.

I think a lot of it just comes from people blindly copying examples online without taking the time to understand what it is they're copying.

 

That's one difference between 'web developers' and programmers. If you can afford Dreamweaver (or know how to spell KompoZer), and you can convince people to give you money, you're a 'web developer'.