Accessing Databases with DB - Understanding Query Information

(Page 5 of 7 )

In addition to methods that send queries to the database and return results, DB provides some methods that return information about a query. These methods tell you the size of the result set returned or modified by a query.

DB_Result::numRows()

The numRows() method returns the number of rows in a result set. This is useful for checking if any rows were selected before printing them:

$res = $dbh->query('SELECT flavor FROM ice_cream
       WHERE price < 5');
if ($res->numRows() > 0) {
     print "Your choices: <ul>";
     while($row = $res->fetchRow())
          { print "<li> $row[0]</li>"; }
     print "</ul>";
} else { 
     print "No flavors available for less than five
            dollars.";
}

The numRows() method is called on the statement handle, not on the database handle. You can only call numRows() after a SELECT query.

DB_Result::numCols()

The numCols() method returns the number of columns in a result set. You can use numCols() to dynamically display information about a table:

$res = $dbh->query('SELECT * FROM ice_cream');
print 'There are '.$res->numCols().' columns in the
       ice_cream table.'; print '<table>';
while($row = $res->fetchRow()) {
     print '<tr>';
     foreach ($row as $val) { print "<td>$val</td>"; }
     print '</tr>';
}
print '</table>';

Like numRows(), the numCols() method is called on the statement handle, not on the database handle. You can only call numCols() after a SELECT query.

DB::affectedRows()

The affectedRows() method returns how many rows were changed by an UPDATE, INSERT, or DELETE query. It is called on the database handle, not the statement handle. This is because the query() method doesn’t return a statement handle for these kinds of queries, just a status code or error object:

$dbh->query("UPDATE ice_cream SET price = price - 1 WHERE
      flavor LIKE 'Chocolate%'");
print 'Discount applied to ' . $dbh->affectedRows()
      . ' Chocolate flavors.';

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today. Buy this book now.

Next: Running a Query Multiple Times >>
 

More PHP Articles
More By David Sklar

Comments On: Accessing Databases with DB

· Is it just me or is the syntax for PEAR DB access, as well as actual calls to the DB...    · dbase -> dBasefbsql -> FrontBaseibase -> InterBaseifx -> Informixmsql ...    · Perhaps it's a Perl DBI rip off, but a least the Perl DBI isn't so bug ridden and...   >>> Post your comment now!