PDOStatement
在线手册:中文  英文

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 语句,有些数据可能返回由此语句返回的行数。但这种方式不能保证对所有数据有效,且对于可移植的应用不应依赖于此方式。

返回值

返回行数。

范例

Example #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");
?>

以上例程会输出:

Return number of rows that were deleted:
Deleted 9 rows.

Example #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
在线手册:中文  英文

用户评论:

user at holland dot nl (2013-05-30 14:46:06)

for InnoDB users; note; that rowCount will give 1 for updated AND inserted rows... so if you want to check if a row is updated, you have to find another solution

Mike Robinson (2013-02-08 05:13:59)

Please note that using PDOStatement::rowCount with a MySQL SELECT statement returns the number of rows found by that statement, not the number of rows affected (which would obviously be 0).

Daniel Karp (2012-08-31 00:09:13)

Note that an INSERT ... ON DUPLICATE KEY UPDATE statement is not an INSERT statement, rowCount won't return the number or rows inserted or updated for such a statement. For MySQL, it will return 1 if the row is inserted, and 2 if it is updated, but that may not apply to other databases.

leandro at marquesini dot com (2012-01-03 10:04:01)

To display information only when the query is not empty, I do something like this:

<?php
    $sql 
'SELECT model FROM cars';
    
$stmt $db->prepare($sql);
    
$stmt->execute();
    
    if (
$data $stmt->fetch()) {
        do {
            echo 
$data['model'] . '<br>';
        } while (
$data $stmt->fetch());
    } else {
        echo 
'Empty Query';
    }
?>

dcahh at gmx dot de (2011-08-19 06:53:35)

It's pretty obvious, but might save one or the other from bug tracking...

Alltough rowCount ist returned by the statement, one has to execute the statement before rowCount returns any results...

Does not work
<?php
    $statement 
$dbh->prepare('SELECT FROM fruit');
    
$count $statement->rowCount();
?>

Works
<?php
    $statement 
$dbh->prepare('SELECT FROM fruit');
    
$statement->execute();
    
$count $statement->rowCount();
?>

Ome Ko (2011-07-16 12:08:41)

When updating a Mysql table with identical values nothing's really affected so rowCount will return 0. As Mr. Perl below noted this is not always preferred behaviour and you can change it yourself since PHP 5.3.

Just create your PDO object with 
<? php
$p = new PDO($dsn, $u, $p, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
?>
and rowCount() will tell you how many rows your update-query actually found/matched.

php at alishabeth dot com (2009-01-20 17:15:04)

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 (2008-11-19 11:32:36)

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 (2008-11-19 11:30:27)

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 (2008-06-03 03:19:27)

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 (2007-12-26 04:10:33)

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'$idPDO::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 (2007-11-14 07:06:29)

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 (2007-10-07 09:22:07)

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.

易百教程