PHP 5.6.0RC3 is available

PDOStatement::rowCount

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

PDOStatement::rowCount Devuelve el número de filas afectadas por la última sentencia SQL

Descripción

public int PDOStatement::rowCount ( void )

PDOStatement::rowCount() devuelve el número de filas afectadas por la última sentencia DELETE, INSERT, o UPDATE ejecutada por el correspondiente objeto PDOStatement.

Si la última sentencia SQL ejecutada por el objeto PDOStatement asociado fue una sentencia SELECT, algunas bases de datos podrían devolver el número de filas devuelto por dicha sentencia. Sin embargo, este comportamiento no está garantizado para todas las bases de datos y no debería confiarse en él para aplicaciones portables.

Valores devueltos

Devuelve el número de filas.

Ejemplos

Ejemplo #1 Devolver el número de filas eliminadas

PDOStatement::rowCount() devuelve el número de filas afectadas por una sentencia DELETE, INSERT, o UPDATE.

<?php
/* Borrar todas las filas de la tabla FRUIT */
$del $gbd->prepare('DELETE FROM fruit');
$del->execute();

/* Devolver el número de filas que fueron eliminadas */
print("Devolver el número de filas que fueron eliminadas:\n");
$cuenta $del->rowCount();
print(
"Eliminadas $cuenta filas.\n");
?>

El resultado del ejemplo sería:

Devolver el número de filas que fueron eliminadas:
Eliminadas 9 filas.

Ejemplo #2 Contar las filas devueltas por una sentencia SELECT

Para muchas bases de datos, PDOStatement::rowCount() no devuelve el número de filas afectadas por una sentencia SELECT. En su lugar, use PDO::query() para ejecutar una sentencia SELECT COUNT(*) con las mismas declaraciones que la sentencia SELECT pretendida, luego use PDOStatement::fetchColumn() para recuperar el número de filas que serán devueltas. La aplicación podrá entonces realizar la acción correcta.

<?php
$sql 
"SELECT COUNT(*) FROM fruit WHERE calories > 100";
if (
$resultado $conexión->query($sql)) {

    
/* Comprobar el número de filas que coinciden con la sentencia SELECT */
  
if ($resultado->fetchColumn() > 0) {

        
/* Ejecutar la sentencia SELECT real y trabajar con los resultados */
         
$sql "SELECT name FROM fruit WHERE calories > 100";
       foreach (
$conexión->query($sql) as $fila) {
           print 
"Nombre: " .  $fila['NAME'] . "\n";
         }
    }
    
/* No coincide ningua fila -- hacer algo en consecuencia */
  
else {
      print 
"Ninguna fila coincide con la consulta.";
    }
}

$resultado null;
$conexión null;
?>

El resultado del ejemplo sería:

apple
banana
orange
pear

Ver también

add a note add a note

User Contributed Notes 11 notes

up
13
Daniel Karp
1 year 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
12
Ome Ko
3 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
21
Matt
6 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
10
leandro at marquesini dot com
2 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
4
user at nospam dot example dot com
10 months 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
6
gunnrosebutpeace at gmail dot com
6 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
1
sERGE-01
8 months 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
2
phpnet at maya-control dot ro
3 months ago
Yet another workaround to return the row count inside only ONE select (see limitations below!):

$sth = $dbh->prepare("SELECT *,count(*) AS howmany FROM users WHERE email=:email and password=:pass"); #var placeholders
$sth->execute(array(':email'=>$email, ':pass'=>$pass)); #var binding
$row = $sth->fetch(); #get one row (it'll always be one and only one!!!)

if ($row['howmany'] == 1){ #we have a match and only one! cool!
    echo $row['email'], $row['name'], $row['phone'], ... ;
} elseif ($row['howmany']>1) { #more than one row returned
    #one programmer should be fired 'cause he's not checking for
    #for existing emails, before creating a new user
    ...
    # treat this exception somehow or simply skip this branch,
    # if you're sure it won't happen in your table
} else { #no match in the table ($row['howmany'] == 0)
    echo "Email/pass didn't match the ones in the database!";
}

Advantages:
- only one select statement is executed, no two steps needed!
- it checks if one row exists in the table or not, according to the WHERE clause.
- it returns all (or only a selection of) fields for that one row, if exists.

Disadvantages:
- it doesn't return row fields reliable if more than one row found. If more than one row responds to the SELECT query, the query returns still only one row and you don't know which one exactly .
Maybe using a SORT BY, would make it a bit more predictible (as in: "if more than one users found, return the last user added in the table") but it's more a matter of good design of the program that fills in the table initially.

Uses:
- It is perfect for checking if a user/pass pair is present in a users table and to return the other fields of the user (like name, phone, whatever) if user was found.
up
-2
e dot sand at elisand dot com
5 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
e dot sand at elisand dot com
5 years ago
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']}";
}
?>
up
-16
dcahh at gmx dot de
2 years ago
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();
?>
To Top