What are the limitations of PDO and select statments

Question

Can PDO return the number of rows in a db when using a SELECT staement? And does php even support named parameters?

Answer 1

Can PDO return the number of rows in a db when using a SELECT staement?

There are no limitations in selecting whatever data from db.
PDO can return number of rows in a table as well as any other information you may wish to select from database. Just select it. And you will have it without any problem. That's all.

Answer 2

Can PDO return the number of rows in a db when using a SELECT staement?

I imagine that confusion stems from this sentence in the manual:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

http://php.net/manual/en/pdostatement.rowcount.php

It's the database which supports or does not support this feature. When you issue a SELECT * FROM foo ... statement, you're telling the database you'd like to get all the data in the foo table [according to some conditions]. The emphasis here being on data. The database will evaluate all the data it has for that table to give back to you what you asked for. This may or may not be helped by some indices, this may or may not happen asynchronously, the database may or may not know how many results it'll be able to deliver. This all depends on the internals of the database. In some forms of storage, the database simply may not know how many results there will be until it has actually fetched all of them.

Therefore when you call PDOStatement::rowCount, the result may or may not be accurate depending on the database. The PDO API just exposes whatever the database supplies, and since PDO can be used to talk to many different databases, its capabilities depend on what database exactly you're talking to.

And does php even support named parameters?

PHP?! Yes, PHP has many database APIs which support parameterized queries with named parameters. PDO is among them.

Answer 3

The following example shows how to get the rows that was selected or affected in PDO:

<?php
    $selectsql = $database->prepare('SELECT * FROM table');
    $selectsql->execute();
    $count = $selectsql->rowCount();
?>

OR

<?php
    $selectsql = $database->prepare('SELECT COUNT(*) FROM table');
    $selectsql->execute();
?>

With regards to Named Parameters

https://wiki.php.net/rfc/namedparameters

Named Parameters is a topic that has been raised many times over the years. The pros and cons are captured in a variety of emails and (obviously) the feature has not gained support and is not implemented. Since the topic continually gets re-raised, and now PHP has an RFC process, the discussion should be recorded in an RFC (Note this is yet to be done) so the same arguments don't have to be revisited.

I did however, find some way of simulating it:

Simulating PHP Named Parameters

PHP and the lack of Named Parameters

Answer 4

When using the MySQL driver, these pieces of code are equivalent

// using rowcount
$stmt = $pdo->prepare('SELECT * from `foo` WHERE `bar` = :bar');
$stmt->execute(array('bar' => 'bar'));
$count = $stmt->rowCount();

// using a COUNT query
$stmt = $pdo->prepare('SELECT COUNT(1) from `foo` WHERE `bar` = :bar');
$bar = 'bar';
$stmt->bindParam('bar', $bar);
$stmt->execute();
$count = $stmt->fetchColumn();

I've also illustrated using named placeholders in the query (which is entirely possible).

The first piece of code will most probably not work with another DBMS whereas the second will.