Query information

Switching to an abstract database system might sound worrying at first - particularly when you consider the variety of DBMSs out there, each with their own dialect of SQL. However, PEAR::DB does a remarkably good job of getting cross-platform issues supported, which means you need no longer worry about porting code from one platform to another.

Three very useful functions that would have been tricky to use without this abstraction are numRows(), numCols(), and affectedRows(). These functions return information about what a query actually did - numRows() returns how many rows were returned from a SELECT statement, numCols() returns how many columns (fields) were returns from a SELECT statement, and affectedRows() returns how many rows were altered by an UPDATE, INSERT, or DELETE statement. For example, if we have three rows with 0 NumVisits in our people table and execute the query "UPDATE people SET Name = 'xxx' WHERE NumVisits = 0;", affectedRows() would return 3.

Here is an example of these functions in action, based upon the first PEAR::DB script we looked at earlier:

<?php
    include_once('DB.php');
    $db = DB::connect("mysql://root:alm65z@localhost/phpdb");

    if (DB::isError($db)) {
        print $db->getMessage();
        exit;
    } else {
        $result = $db->query("SELECT * FROM people;");
        print 'Query returned ' . $result->numRows() . ' rows\n';
        print 'Query returned ' . $result->numCols() . ' cols\n';
        print 'Query affected ' . $db->affectedRows() . ' rows\n';
        $db->query("INSERT INTO people VALUES ('Thomas', 0);");
        print 'Query returned ' . $result->numRows() . ' rows\n';
        print 'Query returned ' . $result->numCols() . ' cols\n';
        print 'Query affected ' . $db->affectedRows() . ' rows\n';
        $result->free();
    }

    $db->disconnect();
?>

The first part of that script is the same as its predecessor, however things change after the $result = .... line. As you can see, the first PEAR::DB query is a SELECT statement, which means that it will return values for numRows() and numCols() - these are printed out as you would expect. Note that affectedRows() is not a function of the PEAR::DB query result object - numRows() is $result->numRows(), numCols() is $result->numCols(), but affectedRows() is $db->affectedRows().

The reason for this is because SELECT statements read from the database, and return a result object from $db->query(). INSERT, UPDATE, and DELETE statements only return success or failure, and because affectedRows() only returns a meaningful value when used with these types of statement, it would be pointless to put affectedRows() into the query() result.

This is illustrated in the next block of code - this time we insert a new person into the table, and again print out the three functions. Note that we do not capture the return value of the function this time, because it does not return anything useful in this script. This time around, printing out numRows() and numCols() returns the same values as before - this is because the $result object is unchanged from the previous call.

However, calling $db->affectedRows() should return 1, because we inserted a row. To illustrate the situation with the return value of query(), try editing the code to this:

$result = $db->query("INSERT INTO people VALUES ('Thomas', 0);");

This time you should get the following error when you try to run the script:

The reason for this is because the return value from query() will be true if it succeeded, or an error otherwise. As a result, calling $result->numRows() is calling a function on "true", which will not work.

So, as you can see, use numRows() and numCols() only with SELECT queries, and use affectedRows() only with INSERT, UPDATE, and DELETE queries.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.