PHPのお勉強！

PHP TOP
PHP 8.4.2 Released!

    PDOStatement::rowCount

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

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

    説明

    public PDOStatement::rowCount(): int

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

    SELECT文のような、結果セットを生成するSQLステートメントについては動作が未定義です。 動作はドライバによって異なる可能性があります。 データベース(例: MySQL のバッファリングモード)によっては、文によって返された行数を返すかもしれません。しかしながら、 この振る舞いは全てのデータベースで保証されていません。 さまざまな場所で使用するアプリケーションでは、 これに頼ってはいけません。

    注意:

    このメソッドは SQLite ドライバの場合はあらゆる場合に "0" (ゼロ) を返します。 PostgreSQL ドライバの場合は ステートメント属性 PDO::ATTR_CURSOR の設定が PDO::CURSOR_SCROLL の場合にだけ "0" (ゼロ) を返します。

    パラメータ

    この関数にはパラメータはありません。

    戻り値

    行の数を返します。

    エラー / 例外

    PDO::ATTR_ERRMODE が PDO::ERRMODE_WARNING に設定されていた場合、E_WARNING レベルのエラーが発生します。

    PDO::ATTR_ERRMODE が PDO::ERRMODE_EXCEPTION に設定されていた場合、PDOException がスローされます。

    例

    例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.

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

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

    <?php
    $sql     = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
    $res = $conn->query($sql);
    $count = $res->fetchColumn();
    print     "There are " . $count . " matching records.";

    上の例の出力は、 たとえば以下のようになります。

    There are 2 matching records.

    参考

    Found A Problem?

    Learn How To Improve This Page • Submit a Pull Request • Report a Bug
    ＋add a note

    User Contributed Notes 13 notes

    up
    89
    Ome Ko
    13 years ago
    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.
    up
    42
    Matt
    17 years ago
    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.
    up
    33
    Daniel Karp
    12 years ago
    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.
    up
    25
    leandro at marquesini dot com
    12 years ago
    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';
    }
    ?>
    up
    18
    gunnrosebutpeace at gmail dot com
    16 years ago
    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();
    ?>
    up
    12
    sERGE-01
    11 years ago
    In some drivers rowCount() only works when using the prepare() with PDO::CURSOR_SCROLL
    So, you can modify PDO class:

    <?php
    class myPDO extends PDO
    {
    function     query($query, $values=null)
    {
    if(    $query == "")
    return     false;

    if(    $sth = $this->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)))
    {
    $res = ($values) ? $sth->execute($values) : $sth->execute();
    if(!    $res)
    return     false;
    }
    return     $sth;
    }
    }
    ?>

    Now let's test (i using php 5.2.9-2):

    <?php
    function TestRowCount($dsn, $db_user, $db_pass)
    {
    $pdh = new PDO($dsn, $db_user, $db_pass);
    $sth = $pdh->query("SELECT * FROM sys.tables");
    print     "rowCount() Standart: ".$sth->rowCount()."<br>";

    $pdh = new myPDO($dsn, $db_user, $db_pass);
    $sth = $pdh->query("SELECT * FROM sys.tables");
    print     "rowCount() New: ".$sth->rowCount()."<br><br>";

    $pdh=null;
    }

    $db_server = "xxx";
    $db_name = "xxx";
    $db_user = "xxx";
    $db_pass = "xxx";

    print     "PDO_MSSQL"."<br>";
    TestRowCount("mssql:host=$db_server;dbname=$db_name", $db_user, $db_pass);

    print     "MSSQL throw PDO_ODBC"."<br>";
    TestRowCount("odbc:DRIVER={SQL Server};SERVER=$db_server;DATABASE=$db_name;", $db_user, $db_pass);

    print     "MS SQL driver 2.0"."<br>";
    TestRowCount("sqlsrv:server=$db_server;Database=$db_name", $db_user, $db_pass);
    ?>

    My results:
    -------------------
    PDO_MSSQL
    rowCount() Standart: 0
    rowCount() New: 0

    MSSQL throw PDO_ODBC
    rowCount() Standart: -1
    rowCount() New: 53

    MS SQL driver 2.0
    rowCount() Standart: -1
    rowCount() New: 53
    -------------------

    With myPDO class you can use prepared queries like:
    <?php
    $pdh     = new myPDO($dsn, $db_user, $db_pass);
    $sth = $pdh->query("select * from data where id>? or name like ?", array(100, "A%"));
    ?>
    up
    5
    xmt at abv dot bg
    6 years ago
    Note:
    =====
    On Mysql SELECT statement with Buffered queries, rowCount will return the correct count of the items in the result set.
    BUT if your query is unbuffered, than it will return 0. No matter if all the rows are retrieved from the result set or not (while in mysqli this behaviour is different - you will still get the number of items in the result set with unbuffered queries, but only when you retrieve all the rows from the set).

    Example:
    ========
    $conn = new PDO("mysql:host=127.0.0.1;dbname=db", 'root', 'root');

    // use unbuffered query
    $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

    $stmt = $conn->query("select * from towns");

    echo $stmt->rowCount(); // will always return 0
    up
    8
    user at nospam dot example dot com
    11 years ago
    MySQL does not seem to return anything in rowCount for a select statement, but you can easily and efficiently get the row count as follows:

    class db extends PDO {
    public function last_row_count() {
    return $this->query("SELECT FOUND_ROWS()")->fetchColumn();
    }
    }

    $myDb = new db('mysql:host=myhost;dbname=mydb', 'login', 'password' );

    Then, after running your query:

    if ( $myDb->last_row_count() == 0 ) {
    echo "Do something!";
    }
    up
    2
    info at buylikesandviews dot com
    9 years ago
    every good work
    If you use "INSERT INTO ... ON DUPLICATE KEY UPDATE" syntax, mysql_affected_rows() will return you 2 if the UPDATE was made (just as it does with the "REPLACE INTO" syntax) and 1 if the INSERT was.

    So if you use one SQL request to insert several rows at a time, and some are inserted, some are just updated, you won't get the real count..
    up
    1
    lsrzj at facebook
    9 years ago
    Well, I woundn't do as suggested querying twice the database to get the count and then get the data I want. It would be simpler and would give better performance to query once and retrieve both, record count and the data itself

    <?php
    $sql     = "SELECT * FROM fruit WHERE calories > :calories";
    $sth = $conn->prepare($sql);
    $sth->bindParam(':calories', 100, PDO::PARAM_INT);
    $res = $sth->execute();
    if (    $res) {
    $record = $sth->fetchAll();
    /* Check the number of rows that match the SELECT statement */
    if (count($record) > 0) {
    foreach (    $record as $row) {
    print     "Name: " . $row['NAME'] . "\n";
    }
    }
    /* No rows matched -- do something else */
    else {
    print     "No rows matched the query.";
    }
    }
    $conn = null;
    ?>
    up
    0
    bohwaz
    5 years ago
    Please note another interesting behavior with PostgreSQL.

    If you try to use rowCount() after a statement has been prepared using the PDO::ATTR_CURSOR set to PDO::CURSOR_SCROLL you will always get zero (0).

    That's because PG doesn't have any way to tell how many rows are in the cursor until it did iterate through all rows.

    <?php
    $st     = $pdo->prepare('SELECT NOW();', [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
    $st->execute();
    var_dump($st->rowCount());
    ?>

    Will return "0", whereas the same statement without the CURSOR_SCROLL attribute, will correctly return 1.

    Please see this bug report https://bugs.php.net/bug.php?id=77855 for details.

    This documentation should be updated shortly to reflect that issue.
    up
    -4
    e dot sand at elisand dot com
    16 years ago
    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.
    up
    -5
    @rhavendc (twitter)
    9 years ago
    We're having problem with these PDOStatement::fetchColumn() and PDOStatement::rowCount(). I don't know if we have alike case to others or this is just a problem in our coding. In local, the rowCount() is not giving the correct number of rows but it is working fine when uploaded to our hosting site.. while the fetchColumn() is the reverse. The fetchColumn() is working fine in local but not anymore when uploaded. I don't know what is really going on with that but I think rowCount() is the best and the others are optional to use.
    ＋add a note