Cloning a PHP PDO recordset object to count the rows returned by a select statement

I'm refactoring part of a PHP application which uses adodb as db library, in order to start using PDO.

I need something which provides me the number of rows included in a select recordset, something that can easily replace the old $rs->RecordCount() adodb method that I used before. It should work for sqlite3.

I can't simply re-execute the query (or re-execute the query using a select count(*) ) because I can't change so much the original application, the application calls in many parts a function get_num_rows($rs) (which contains just $rs->RecordCount() ) and I can just change开发者_C百科 the content of that function.

I tried cloning the recordset object and counting the records fetched:

function get_num_rows($rs)
{
    $rs_copy = clone $rs;
    return (count($rs_copy->fetchAll()));
}

but it doesn't work, because $rs_copy->fetchAll() returns me false. I can't do it on the original recordset because later in the applicaiton I need to fetch it again and I think there is no way in PDO sqlite to reuse the recordset (correct me if I'm wrong).

Do you have any solutions?

Using SELECT COUNT(*) FROM run as a second query is the recommended way to count the number of rows in a result set when using LIMIT in your primary query. It won't fetch the same data twice, it simply counts how many rows are found and returns 1 row, an integer. Another way to find the number of rows when using the LIMIT statement is to use SELECT SQL_CALC_FOUND_ROWS (field list) FROM ... in your first query, then run SELECT FOUND_ROWS() to get the number of rows, however, this method is slower than the previous one mentioned. Be aware that running count() in PHP will only give you the number of rows returned, not the actual number of rows found in the database (if you're using LIMIT).

Cheers

Maybe a possibility is to do like col shrapnel suggested and store the result in an array. You might be able to keep the interface clean by extending PDOStatement and then

http://www.php.net/manual/en/pdo.setattribute.php PDO::ATTR_STATEMENT_CLASS

So it will return you EugenioEnhancedPDOStatement

Thanks to all. I have found an easier, quick and dirty solution, my new get_num_rows() will be something like:

$temp_ar = explode("FROM", $rs->queryString);

$sql = "SELECT COUNT(*) FROM ".$temp_ar[1];

$conn->query($sql);

...

You can't properly clone and/or serialize/unserialize PDO objects, most probably because they are tightly tied to a connection resource.

The fact is, there is no point cloning it. If $rs is a PDOStatement object, you can just re-execute it, since you have access to it. This is one of the main advantage of prepared statements, prepare once, execute multiple times.

Like this:

function get_num_rows($rs)
{
 $rs->execute();
 return (count($rs->fetchAll()));
}

You guessed it: calling execute() on the same PDOStatement instance the second time without specifying any parameters will reuse the last set of parameters and re-execute the statement as is.

Therefore, there is no need to clone PDOStatement objects.