PDOStatement->nextRowset
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';
}
?>
PDOStatement->rowCount
(PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)
PDOStatement->rowCount — Son SQL deyiminden etkilenen satır sayısını döndürür
Açıklama
int PDOStatement::rowCount
( void
)
PDOStatement nesnesi tarafından çalıştırılan son DELETE, INSERT veya UPDATE deyiminden etkilenen satır sayısını döndürür.
Eğer PDOStatement nesnesi ile ilişkili deyim bir SELECT deyimi ise bazı veritabanları bu deyimden etkilenen satır sayısını da döndürebilmektedir. Ancak bu davranış tüm veritabanları için garanti edilmediğinden taşınıbilir bir uygulamada bu davranışa itibar edilmemelidir.
Dönen Değerler
Etkilenen satır sayısı.
Örnekler
Örnek 1 - Silinen satır sayısının döndürülmesi
PDOStatement::rowCount() bir DELETE, INSERT veya UPDATE deyiminden etkilenen satır sayısını döndürür.
<?php
/* FRUIT tablosundaki tüm satırları silelim */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();
/* Silinen satır sayısını döndürelim */
$count = $del->rowCount();
print("$count satır silindi.\n");
?>
Yukarıdaki örneğin çıktısı:
9 satır silindi.
Örnek 2 - Bir SELECT deyiminden etkilenen satırların sayılması
Çoğu veritabanında PDOStatement::rowCount() bir SELECT deyiminden etkilenen satır sayısını döndürmez. Bu bakımdan, SELECT deyiminde kullandığınız yüklemlerin kullanıldığı bir SELECT COUNT(*) deyimi ile yapacağınız bir PDO::query() çağrısından sonra bir PDOStatement::fetchColumn() çağrısı ile etkilenen satır sayısını elde edebilirsiniz.
<?php
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {
/* Bu deyimden etkilenen satır sayısına bakalım */
if ($res->fetchColumn() > 0) {
/* Gerçek SELECT deyimini kullanarak sonuçları alabiliriz */
$sql = "SELECT name FROM fruit WHERE calories > 100";
foreach ($conn->query($sql) as $row) {
print "Meyve: " . $row['NAME'] . "\n";
}
}
/* Etkilenen satır yokmuş; gereğini yapalım */
else {
print "Sorguyla eşleşen satır yok.";
}
}
$res = null;
$conn = null;
?>
Yukarıdaki örneğin çıktısı:
apple banana orange pear
Ayrıca Bakınız
- PDOStatement::columnCount() - Sonuç kümesindeki sütun sayısını döndürür
- PDOStatement::fetchColumn() - Sonuç kümesindeki sonraki satırdan tek bir sütun döndürür
- PDO::query() - Bir SQL deyimini çalıştırıp sonucu bir PDOStatement nesnesi olarak döndürür
add a note
User Contributed Notes
PDOStatement->rowCount
leandro at marquesini dot com
03-Jan-2012 03:04
dcahh at gmx dot de
19-Aug-2011 12:53
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
16-Jul-2011 06:08
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
20-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
19-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
19-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
02-Jun-2008 09: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
25-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
14-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
07-Oct-2007 03: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.