Override the Query Methods

The simple solution to this problem is inheritance. On the surface, this would seem fairly straightforward: Create a class that extends SQLiteDatabase and override all the query methods. If errors arise within those overridden methods, simply throw an exception. In this way, the messy details of error trapping can be buried inside the class file and a single catch block can handle all errors. The first five methods in the class definition file shown in Listing 15-4 do exactly this.

Listing 15-4. Extending the SQLiteDatabase class

//////////////////////////////////////////////////////////////
//public functions related to queries
/**
Override function
*/
    public function query($strsql, $type = SQLITE_BOTH, &$err_msg = ''){
    //SQLiteResult query ( string query [, int result_type [, string &error_msg]] )
        if ( false === $result = parent::query($strsql, $type, $err_msg)){
            //no sql details with last error
            throw new SQLiteException (sqlite_error_string($this->lastError()));
        }
        return $result;
    }
//////////////////////////////////////////////////////////////
/**
Override function
*/
    public function unbufferedQuery($strsql, $type = SQLITE_BOTH, &$err_msg = ''){
        //SQLiteUnbuffered unbufferedQuery ( string query [, int result_type [,
string &error_msg]] )
        if ( false === $result = parent::unbufferedQuery($strsql, $type, $err_msg)){
            throw new SQLiteException (sqlite_error_string($this->lastError()));
        }
        return $result;
    }
//////////////////////////////////////////////////////////////
/**
Override function
*/
   public function singleQuery($strsql, $first_column = true, $bin_decode = false){
       //array sqlite_single_query ( resource db, string query [, bool first_row_only
[, bool decode_binary]] )
        if ( false === $result = parent::singleQuery($strsql, $first_column,
$bin_decode)){
            throw new SQLiteException (sqlite_error_string($this->lastError()));
        }
        return $result;
    }
//////////////////////////////////////////////////////////////
/**
Override function
*/
   public function queryExec($strsql, &$err_msg = ''){
       //bool queryExec ( string query [, string &error_msg] )
       if ( !parent::queryExec($strsql, $err_msg)){
            throw new SQLiteException (sqlite_error_string($this->lastError()));
       }
       return true;
   }
//////////////////////////////////////////////////////////////
/**
Override function
*/
    public function arrayQuery($strsql, $type = SQLITE_BOTH, $bin_decode = false ){
   //array arrayQuery ( string query [, int result_type [, bool decode_binary]] )
       if ( false === $result = parent::arrayQuery($strsql, $type, $bin_decode)){
            throw new SQLiteException (sqlite_error_string($this->lastError()));
       }
       return $result;
   }
//////////////////////////////////////////////////////////////

In each case, the query method of the parent class, SQLiteDatabase, is redefined to include a test of the return value.

Error Messages

The comment immediately inside each method definition shows the method prototype as defined on the PHP site. This is especially useful because it shows the type of object returned. Some of the base class methods take an optional string reference argument (&$error_msg).

Note:

In versions of PHP prior to 5.1, passing in this string reference results in this warning: SQLiteDatabase::query() expects at most 2 parameters, 3 given.

The reason a third parameter is necessary is explained as follows (from http://php.net/sqlite_query):

... [$error_msg] will be filled if an error occurs. This is especially important because SQL syntax errors can't be fetched using the [sqlite_last_error()] function.

Quite true. The sqlite_last_error function returns an uninformative message: SQL logic error or missing database. Our code doesn't make use of this error message but this isn't an insurmountable problem. A more specific error message would certainly help in the debugging process, however. Fortunately, if you have warnings turned on while you are developing, you will get something more meaningful. Forcing a warning by referencing a nonexistent table results in the following, more specific, output:

Warning: SQLiteDatabase::query()[function.query]: no such
 table: tblnonexistent...

Query Methods

Look again at Listing 15-4. It includes the five methods for creating result sets. The buffered and unbuffered methods are fairly self-explanatory—you are probably quite familiar with the equivalent MySQL functions. However, MySQL (prior to the MySQL improved extension) has nothing to match the singleQuery, queryExec, or arrayQuery methods. Let's look at these methods in more detail.

The singleQuery method is a recent addition, and the PHP site warns that it is not currently documented. Let's carry on regardless because this method looks especially useful for those situations where a query returns only one row—when using the COUNT function to return the number of records in a table, for example. Here's one view of how this method ought to behave: This method returns only one record, and no result set is created. If the second argument is false, the value returned is an array of the first row. If the second argument is true, then only the first column of the first row is returned, and it is returned as a scalar value.

This speculation may make the best sense of how this method ought to work, but it doesn't describe what actually happens. In fact, this method only ever returns the first column and any number of rows. If the second argument is false, then an array is returned; if the second argument is true and only one row is returned, a scalar is returned. On the PHP site, this second argument is identified as bool first_row_only and the return type is identified as an array. It looks like the return type should be mixed. In any case, this method doesn't yet work the way it ought to. We were warned.

There is no requirement that you use the singleQuery method instead of query. As with MySQL, you can always create a result set and then use the appropriate fetch function to retrieve the value of the first row or a specific field. But why return an object or an array when all that's needed is the value of one column? You may use the singleQuery method for any kind of query—data manipulation or otherwise—but it was designed specifically for situations where a single value or single column is returned, and is presumably optimized for this situation.

As you can see, there is also an arrayQuery method. Like the singleQuery method, this method allows us to directly copy results into an array, bypassing the intermediate step of creating a result set. This method is best used when a limited number of records are returned.

MySQL versions prior to 4.1 have no equivalent to the queryExec method of SQLite because queryExec is specifically designed for use with multiple queries. Multiple, semicolon-separated queries may be passed as a single query string to queryExec. (The install script uses this method to create the tables, triggers, and views and to insert records into the tblresources table.) This method gives significant performance improvements over repeated querying and performs the same job as the MySQL-improved (the mysqli functions added to PHP 5 to support MySQL 4.1) method, mysqli_multi_query. If you like, you can of course use this method to execute a single query.

query

Use of this method to create an SQLiteResult object is shown in Listing 15-5.

Listing 15-5. query method returns a buffered result set

$db = new SQLiteDatabasePlus('resources.sqlite'),
//alphabet view
$strsql = "SELECT * FROM alphabet";
//use buffered result set to get number of rows
$result = $db->query($strsql);
//create alphabet here
if($result->numRows() > 0){
    echo get_alphabet($result);
}

Remember, an SQLiteResult is buffered so you can use the numRows method with this result set. It is also iterable, so this result set may be used in a foreach loop. In this, SQLite differs from MySQL. Because SQLiteResult implements Iterator, all the iterator methods are present—rewind, next, valid, and current. These methods can be used directly, but their real purpose is to allow an SQLite result set to be used in a foreach loop in exactly the same way that you might use an array. (As you might expect, the rewind method can't be applied to an unbuffered result set.) Only this method and the unbuffered query method return a result set object.

unbufferedQuery

There is no need to buffer the result set returned in Listing 15-6.

Listing 15-6. The unbufferedQuery method

try{
        $db = new SQLiteDatabasePlus('../dbdir/resources.sqlite'),
        $type="Edit";
        //retrieve from db
        $strsql = "SELECT * FROM tblresources ".
                "WHERE id = '$id'";
        //get recordset as row
        $result = $db->unbufferedQuery($strsql);
        $row = $result->fetch();
        //can't use below because returns first column only
        //$row = $db->singleQuery($strsql, false);
        // assume vars same as fields
        while(list($var, $val)=each($row)) {
                $$var=$val;
        }
   }catch(SQLiteException $e){
        //debug msg
        echo  $e->getMessage();
   }
}

This listing shows an unbuffered query. In this case, a functional singleQuery method would be preferable because we know that only one record will be returned. However, given the problems with singleQuery, we use the unbufferedQuery method of an SQLiteDatabase object to create a result set object and then use the fetch method to copy the first row into an array.

arrayQuery

The PHP site warns against using the arrayQuery method with queries that return more than 45 records (a somewhat arbitrary number perhaps, but this method stores results in memory so returning a large number of records can exhaust memory). We've used this method in Listing 15-7.

Listing 15-7. Using arrayQuery

    $db = new SQLiteDatabasePlus('../dbdir/resources.sqlite'),
    $db->createFunction('class_id','set_class_id',0);
    $sql = "SELECT id, url, email, ".
        "(precedingcopy || ' ' || linktext || ' ' || followingcopy) ".
        "AS copy, linktext, reviewed, class_id() AS classid ".
        "FROM tblresources ".
        "ORDER BY id DESC ".
        "LIMIT $recordoffset,". PERPAGE;
    //use arrayQuery
    $resultarray = $db->arrayQuery($sql);
...

As you can see, we know exactly how many records are returned because our SQL has a LIMIT clause. Again, this method allows us to bypass creation of a result set.

singleQuery

The code below uses the singleQuery method and does exactly what we need—it returns a single scalar value rather than a result set or an array.

$totalrecords = $db->singleQuery('Select COUNT(*) FROM
 tblresources', true);

queryExec

This method is commonly used to process a transaction. Use the command-line command .dump to dump your database or view the file dump.sql. You'll see that it is formatted as a transaction. You can recreate an entire database by passing this listing as a string to the queryExec method, as we have done with the install script, db_install_script.php.

The ability to perform multiple queries using one string does raise security issues. When using this query method, it is especially important to filter data in order to avoid a possible SQL injection attack. For more details, see php|architect's Guide to PHP Security.^{[ label]}

^{[ label]} Ilia Alshanetsky, php|architect's Guide to PHP Security (Marco Tabini & Associates, Inc., 2005), 73.