Last updated: Fri, 06 Nov 2009

PDOStatement->rowCount

(PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)

PDOStatement->rowCount — 直近の SQL ステートメントによって作用した行数を返す

説明

int PDOStatement::rowCount ( void )

PDOStatement::rowCount() は相当する PDOStatement オブジェクトによって実行された直近の DELETE, INSERT, UPDATE 文によって作用した行数を返します。

関連する PDOStatement によって実行された直近の SQL ステートメントが SELECT 文の場合、いくつかのデータベースは文によって返された行数を返すかも知れません。しかしながら、この振る舞いは全てのデータベースで保証されていません。さまざまな場所で使用するアプリケーションでは、これに頼ってはいけません。

返り値

行の数を返します。

例

例1 削除された行数を返す

PDOStatement::rowCount() は DELETE, INSERT, UPDATE 文によって作用した行数を返します。


<?php
/* FRUIT テーブルから全ての行を削除する */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();

/* 削除された行数を返す */
print("Return number of rows that were deleted:\n");
$count = $del->rowCount();
print("Deleted $count rows.\n");
?>

上の例の出力は以下となります。

Deleted 9 rows.

例2 SELECT 文によって返された行をカウントする

ほとんどのデータベースでは、PDOStatement::rowCount() は SELECT 文によって作用した行数を返しません。代わりに、 PDO::query() を使って意図する SELECT 文として同様の述部を持つ SELECT COUNT(*) 文を発行し、PDOStatement::fetchColumn() を使って返される行数を取得することができます。そうすることで、アプリケーションは正しい動作をすることができます。


<?php
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {

    /* SELECT 文にマッチする行数をチェックする */
  if ($res->fetchColumn() > 0) {

        /* 実際の SELECT 文を発行し、結果を処理する */
         $sql = "SELECT name FROM fruit WHERE calories > 100";
       foreach ($conn->query($sql) as $row) {
           print "Name: " .  $row['NAME'] . "\n";
         }
    }
    /* 行がマッチしなかった場合 -- 他に何かをする */
  else {
      print "No rows matched the query.";
    }
}

$res = null;
$conn = null;
?>

上の例の出力は以下となります。

apple
banana
orange
pear

参考

PDOStatement::columnCount() - 結果セット中のカラム数を返す
PDOStatement::fetchColumn() - 結果セットの次行から単一カラムを返す
PDO::query() - SQL ステートメントを実行し、結果セットを PDOStatement オブジェクトとして返す

PDOStatement->setAttribute

PDOStatement->nextRowset

Last updated: Fri, 06 Nov 2009

add a note User Contributed Notes
PDOStatement->rowCount

php at alishabeth dot com
21-Jan-2009 10:15


It appears that rowCount behavior is different on Mysql 5.0 vs 5.1.  Here's the test I ran:



<?php

$db = new PDO('mysql:host=localhost;dbname=test','test','test');

$sql = "SELECT 1";

$st = $db->prepare($sql);

$st->execute();

print_r($st->rowCount());

?>



Mysql 5.0.45, PHP 5.2.5 returned 1



Mysql 5.1.30, PHP 5.1.6 returned 0



I know... I need to test against same php versions... buy I'm getting lazy...

e dot sand at elisand dot com
20-Nov-2008 04:32


My rowCount() workaround & how it's used:



<?php

class MyPDO extends PDO {

    private $queryString;



    public function query(/* ... */) {

        $args = func_get_args();

        $this->queryString = func_get_arg(0);



        return call_user_func_array(array(&$this, 'parent::query'), $args);

    }



    public function rowCount() {

        $regex = '/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/i';

        if (preg_match($regex, $this->queryString, $output) > 0) {

            $stmt = parent::query("SELECT COUNT(*) FROM {$output[1]}", PDO::FETCH_NUM);



            return $stmt->fetchColumn();

        }



        return false;

    }

}



$pdo = new MyPDO("sqlite::memory:");

$result = $pdo->query("SELECT row1, row2 FROM table WHERE something = 5");



if ($pdo->rowCount() > 0) {

    echo "{$result['row1']}, {$result['row2']}";

}

?>

e dot sand at elisand dot com
20-Nov-2008 04:30


As of SQLite 3.x, the SQLite API itself changed and now all queries are implemented using "statements".  Because of this, there is no way for PDO to know the rowCount of a SELECT result because the SQLite API itself doesn't offer this ability.



As a workaround, I created my own rowCount() function - it's a bit of a hack and hasn't been fully tested yet (I don't know how it will work when using JOINs in SELECTs, etc...), but at least alleviates the necessity for SELECT COUNT(*)'s everywhere in your code.



I would have preferred if it were possible to overload the rowCount() function from PDOStatement, but I don't think it's possible (or I don't know how to do it).  There's also potential room for a bit more security ensuring that $queryString is wiped clean after other query()s so that you don't get a bad result, etc...



The actual code should be posted in the above/below post (max post limits, argh!).  If others wish to extend/perfect this method, please keep me posted with an email as to what you've done.

gunnrosebutpeace at gmail dot com
03-Jun-2008 07:19


It'd better to use SQL_CALC_FOUND_ROWS, if you only use MySQL. It has many advantages as you could retrieve only part of result set (via LIMIT) but still get the total row count.


code:


<?php


$db = new PDO(DSN...);


$db->setAttribute(array(PDO::MYSQL_USE_BUFFERED_QUERY=>TRUE));


$rs  = $db->query('SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 5,15');


$rs1 = $db->query('SELECT FOUND_ROWS()');


$rowCount = (int) $rs1->fetchColumn();


?>

Anonymous
26-Dec-2007 09:10


The rowCount method does not seem to work with pdo_sqlite, maybe because it will not support forward-only cursors:



<?php

error_reporting(E_ALL+E_NOTICE);



$dsn = 'sqlite::memory:';

$pdo = new PDO($dsn);



$pdo->exec('CREATE TABLE foo(id varchar(11) NOT NULL, PRIMARY KEY(id))');

$pdo->exec("INSERT INTO foo(id) VALUES ('ffs')");



$sqlGet = 'SELECT * FROM foo WHERE id=:id';

$stmt = $pdo->prepare($sqlGet);

$id = 'ffs';

$stmt->bindParam(':id', $id, PDO::PARAM_STR);

$stmt->execute();



var_dump($stmt->rowCount(), count($stmt->fetchAll()));

?>



which outputs: 0 1



http://php.net/manual/en/function.sqlite-num-rows.php says sqlite_num_rows() cannot be used on unbuffered results; the explanation to the failure of the rowCount() method is probably along the same lines.

The workaround would be to use the count() function on a fetched result, but it might not be as efficient.

Mr. Perl
15-Nov-2007 12:06


To Matt,



PDO is very similar in design to Perl's DBI which does allow you to set driver specific attributes such as mysql_client_found_rows=1 as part of the DSN. 



PDO has a setAttribute() method, but afaik there is no 

MYSQL_ATTR_CLIENT_FOUND_ROWS constant (yet). Hopefully some PDO developer will patch it in one day.



Setting that (at least in Perl and C) will make rowCount() return the number of rows selected for the update, not just the number of rows actually updated.

Matt
08-Oct-2007 01:22


Great, while using MySQL5, the only way to get the number of rows after doing a PDO SELECT query is to either execute a separate SELECT COUNT(*) query (or to do count($stmt->fetchAll()), which seems like a ridiculous waste of overhead and programming time.



Another gripe I have about PDO is its inability to get the value of output parameters from stored procedures in some DBMSs, such as SQL Server.



I'm not so sure I'm diggin' PDO yet.

evangun2001 at yahoo dot fr
11-Sep-2007 02:59


hipska at mac dot com : what you say is not correct.

The resulting $fruits cannot be used as an array, and count($fruits) always equals 1.



The confusion is however understandable, since foreach ($fruits->query($query) as $row){} will work, as with any array.



The most efficient way to retrieve number of rows found by SELECT in a portable application is probably to make a condition :



- Check that the current database system (get its name via getAttribute()), is one where rowCount() returns the number of rows found

1) If it does, there you have it

2) If it does not, execute an extra COUNT query.

pcdinh at phpvietnam dot net
21-Mar-2006 07:39


In some case you need to know how many row are retrieved from a select query (i.e to page a result set) you can use:



$totalRows = count($resultSet->fetchAll());



It works on MySQL 5.0.18 and PHP 5.1

Pablo Godel
31-Jan-2006 05:18


For large resultsets the method described below is not very efficient. It is better to do a select COUNT(*).

add a note

PDOStatement->setAttribute

PDOStatement->nextRowset

Last updated: Fri, 06 Nov 2009