Is there some reason why there is no PDOStatement method to get the number of rows returned by a SELECT query? I find this very bizarre...

I'm guessing that you're the justin that commented on the rowCount() (http://us3.php.net/manual/en/function.pdostatement-rowcount.php) docs in the manual.

rowCount is tricky, because the server doesn't know how many rows there are until it's reached the last one. So, to count the rows, you either need to ask the server to count them, or to fetch them all.

<?php
   $rows = $db->query("SELECT * FROM FOO");
   echo "There were ", count($rows), " rows";
   foreach ($rows as $row) {
      ...
   }
?>

And if you're thinking "urgh, so I have to fetch all the rows if I use this method", then you're starting to understand what mysql and postgresl client libraries are doing under the hood to get you that information.

See also: mysql_unbuffered_query() (http://us3.php.net/mysql_unbuffered_query).

Typos happen to the best of us; that sample should read:

<?php
   $rows = $db->query("SELECT * FROM FOO")->fetchAll();
   echo "There were ", count($rows), " rows";
   foreach ($rows as $row) {
      ...
   }
?>

I'll be the first to admit that I have a high level understanding of things because I skipped over the C/C++ type languages and only have lots of experience with scripting languages.

So, if that is the explanation, then does that mean that when I do a query, the result is just a resource to get the resulting data from the server and the initial response from the server doesn't actually contain the resulting data? So when you did (in older school php):

while ($row = mysql_fetch_assoc($result)) { ...

Was it actually communnicating with the server on each iteration of the loop to grab the next row in the result?

If you've been using mysql_query() and its postgres equivalent, then you've been using buffered queries that pre-fetch the entire dataset before returning control to your PHP script. mysql_fetch_assoc() then iterates over this in-memory cache.

This approach is fine for small result-sets, but as the number of rows you're pulling out gets larger, you start to feel the pain; you have to wait for all 1 million rows to come back before you can start to process the first row. Not only that, but the data is buffered in memory. To make things worse, PHP needs to copy it into its own memory space when returning the data to the script. If you're sloppy about freeing the query resource and you're building up arrays to hold all the rows, you're doubling your memory footprint.

mysql_unbuffered_query was introduced to alleviate this problem, and the trade-off is that you can't determine the rowcount without fetching all the rows. Sound familiar?

This is a common theme among database APIs, particularly the high-end databases.

Is each fetch call communicating with the server? That depends on the underlying implementation. For instance, postgres is always pre-fetched and totally buffered, because its client library doesn't offer an alternative. Oracle allows you to configure a pre-fetch size so that you can fetch a decent sized chunk every so often; this is pre-set to a decent size that will work well for most people. I haven't looked at the mysql internals, but I imagine that it also has a similar approach for unbuffered queries.

What about setting the FetchMode for the whole PDO Object? So you don't need to pass the FetchMode to every Query/Statement in all your scripts.

Here a possible implementation: http://news.php.net/php.pecl.dev/3122

(If you are interested I can try to provide a complete patch against HEAD)

Perhaps I'd call the methode setFetchMode() (like in Statement), not setDefaultFetchMode(). Or an attribute could be used, but I'd prefer a methode.

PDO-1.0.1 changelog states:

"It is highly recommended that you update to PHP 5.1 before using PDO."

Why not PHP 5.0? I'm using 5.1, but last time I tested PECL::PDO with 5.0, it worked fine!

what's with the Firebird driver?

Look into SQL_CALC_FOUND_ROWS if you are not worried about compatibility with any other database other then MySQL.

I used to use mysql_num_rows a lot but now I realize there is always another way to handle it.

akorthaus you might want to check out http://us2.php.net/manual/en/function.pdostatement-setfetchmode.php

because it's the latest greatest PHP release and obsoletes the 5.0 branch. PDO does indeed work with PHP 5.0, and we have no problem with you using it there, but we still recommend that you upgrade to PHP 5.1, because it's simply "better" than 5.0.

There's a lot of "gumph" to copy over to the PDO class from the PDOStatement class, and I'm not looking forward to the day when the two places get out of sync and a bug results.

Maybe I just write code differently from everyone else, but I prefer to specify how I want the data back in each case--it's rare that I want exactly the same fetch mode throughout my apps.

It should be possible to implement this feature by extending PDO and having it use your own custom PDOStatement class that calls PDOStatement::setFetchMode in its constructor.

Is that not sufficient for your needs? I like to measure up the pros and cons before diving in and implementing it.

I'm already stretched pretty thinly across all the other PDO drivers, I don't use firebird and don't have time to get stuck into it. I only know of 1 PHP core developer that uses firebird and he's been too busy to track PDO development.

So, if there's someone out there that can code C and is really into firebird, step up and help get the firebird driver into shape!

There's still the EXPERIMENTAL file in the sources. Are there any functions that should be still marked as experimental in the docs (e.g. PDOStatement::getColumnMeta)?

Wez, perhaps you or someone else has a better idea how to implement it. I'm quite sure the biggest part of the FetchMode settings can be extracted to one function, which can be used from PDO AND PDOStatement, so there will not be much code dublication. The only small/noticable difference will be, that PDO class will not have PDO::FETCH_BOTH hardcoded to pass it as default value to PDOStatement, it will be a variable, which could be changed.

The benefit will be, that you do not need to wrap the methodes returning a PDOStatement object (PDO::query, PDO::prepare), or wrap the whole PDO class and/or you don't need to set the FetchMode across, all over your scripts, if you are not happy with PDO::FETCH_BOTH.

This is the only missing "low-level-feature" of PDO which forces users to extend/wrap PDO. A default FetchMode does not belong into user-space code, because it's the abstraction level of the PDO API.

If you use a convention like "allways use FETCH_OBJ" in your project, you can use PDO, and don't need to wrap at all, if a default fetch mode is available.

@Richard: I know PDOStatement::setFetchMode(), but I don't find it as useful as something like PDO::setDefaultFetchMode(). Have a look at http://news.php.net/php.pecl.dev/3124 for some more reasons.

We have created some PDO ebuilds ( dev-php5/pecl-pdo ) for the new dev-lang/php packages in the "production-ready" branch of Gentoo PHP Overlay (http://svn.gnqs.org/projects/gentoo-php-overlay/).

If you choose the pdo-external USE flag for dev-lang/php , portage will install those PECL packages automatically. If you choose pdo you will get the PDO packages shipped with php-5.1 compiled into the php binaries, so you cannot upgrade them separately.

If you want to use PDO packages (as well as PHP 5.1), you have to add the production-ready directory to PORTDIR_OVERLAY in /etc/make.conf . Have a look at the wiki guides for details.

Congrats on the stable release! I remember your talk at the Zend PHP conference on PDO. It was informative.

Does a PDO Driver exist for MySQL 5.0? The PHP PDO Manual Entry (http://us3.php.net/pdo/) only mentions MySQL 3.x/4.x versions.

MySQL 5 is still young; I think there are some kinks in the client libraries, so if you find any weird behaviour, be sure to check the mysql bug tracker before opening a PHP bug.

Finally got around to really needing to extend pdo and came up with this, also allows you to set your fetch_mode

http://www.cyberlot.net/extendingpdo

no one was able to help me with cursors using the pdo interface.

http://www.sitepoint.com/forums/showthread.php?p=3040612#post3040612 see the bottom to see my current fix.

my fix isn't perfect. is there a better way? If not i'll extend pdo statement so the interface is similar to:

$stmt->executeCursor($sql)
while($row = $stmt->getNextRow()){
}
$stmt->nextRecordSet();//or nextCursorReference();

How can I create/use a "custom PDOStatement"? Do you mean that there's some way to convince PDO to return a custom class (derived from PDOStatement) when PDO->prepare() is called?

It's not documented, but it can be done. Here's a link to the test case in CVS: <a href="http://cvs.php.net/viewvc.cgi/php-src/ext/pdo/tests/pdo_023.phpt?revision=1.8&view=markup">Custom PDOStatement</a>