Handling Result Sets and More with PDO Objects in PHP 5 - Counting affected rows and columns
(Page 4 of 4 )
As I stated in the prior section, the last two methods that I plan to review in this tutorial are aimed specifically at determining the number of database rows affected after performing an insertion, update and deletion operation, in addition to counting the columns present in a data set.
These usual database-related tasks are carried out by the "rowCount()" and "columnCount()" methods respectively, which are bundled with the PDO extension. But first, let me get rid of their theoretical aspects and show you a pair of examples that should dissipate any possible doubts about the way they work.
Look at the following code samples to see these useful methods in action:
// example using the 'rowCount()' method (returns the number of
rows affected by a query)
try{
$dbh=new PDO('mysql:host=localhost;dbname=alejandro','user','password');
$dbh->prepare('DELETE * FROM users WHERE id<20');
$dbh->execute();
$delrows=$dbh->rowCount();
echo 'Number of deleted rows after executing SQL statement is
as following: '.$delrows;
/*
displays the following:
Number of deleted rows after executing SQL statement is as
following: 10
*/
}
catch(PDOException $e) {
echo 'Error : '.$e->getMessage();
exit();
}
// example using the 'columnCount()' method (returns the number
of columns in a result set)
try{
$dbh=new PDO('mysql:host=localhost;dbname=alejandro','user','password');
$dbh->prepare('SELECT * FROM users');
$dbh->execute();
$cols=$dbh->columnCount();
echo 'Number of columns in result set after executing SQL
statement is as following: '.$cols;
/*
displays the following:
Number of columns in result set after executing SQL statement
is as following: 4
*/
}
catch(PDOException $e) {
echo 'Error : '.$e->getMessage();
exit();
}
As you can see, counting affected database rows and columns can be a no-brainer process if you have the assistance of these two methods packaged with the PDO extension. Of course, in this case I'm only demonstrating a rather basic implementation of them, so I recommend that you develop your own testing examples to see more clearly how these methods work.
Final thoughts
Sadly, we've come to the end of this article. As you saw, the PDO library comes armed with a neat arsenal of methods for tackling the most common tasks associated with different database systems.
Nonetheless, to be frank, the power of this PHP library hasn't been completely revealed yet, since there are some remarkable features that need to be reviewed properly, including the implementation of prepared queries with parameters.
All of these interesting topics will be covered in the last part of the series, so I don't think you want to miss it!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
