Upgrading to PHP 5

Object-Oriented Interface

The SQLite extension allows you to interact with SQLite in an object-oriented manner. SQLite’s OO interface turns your database connection into an object and lets you call methods on it. When using this interface, there’s no need to pass in a database handle to any SQLite functions, because the object knows what database connection it should use.

Additionally, the SQLite OO interface lets you iterate directly over queries inside a foreach without needing to call fetch_array( ). PHP will automagically request the appropriate row from SQLite and then stop the loop when you’ve read all the rows.

Using the SQLiteDatabase Object

To use the OO interface, instantiate a new SQLiteDatabase object and call methods on it. Example 4-1 uses this interface to connect to the database /www/support/users.db and SELECT all the rows from the users table.

Example 4-1. Using the SQLite object-oriented interface

$db = new SQLiteDatabase('/www/support/users.db');

// one at a time
$r = $db->query('SELECT * FROM users');
while ($row = $r->fetch( )) {
    // do something with $row
}

// all at once
$r = $db->arrayQuery('SELECT * FROM users');
foreach ($r as $row) {
    // do something with $row
}

unset($db);

All procedural SQLite functions are available under the object-oriented interface, but their names are not identical. For one, you must remove the leading sqlite_ from the function name. Also, names use studlyCaps instead of underscores.

Additionally, you don’t pass in the database link identifier, since that’s stored in the object. So, sqlite_query($db, $sql) becomes $db->query($sql), and so forth.

The major exception to these rules is sqlite_close( ). To end the connection when using the OO interface, delete the object by using unset( ).

Table 4-2 contains a list of frequently used SQLite functions and their object equivalents.

Table 4-2. SQLite functions

Procedural name	Object-oriented name
$db = sqlite_open($table)	$db = new SQLiteDatabase($table)
sqlite_close($db)	unset($db)
$r = sqlite_query($db, $sql)	$r = $db->query($sql)
$r = sqlite_query_array($db, $sql)	$r = $db->arrayQuery($sql)
$r = sqlite_query_unbuffered($db, $sql)	$r = $db->unbufferedQuery($sql)
sqlite_fetch_array($r)	$r->fetch( )
sqlite_fetch_single($r)	$r->fetchSingle( )
$safe = sqlite_escape_string($s)	$safe = $db->escapeString($s)
$id = sqlite_last_insert_rowid($r)	$id = $db->lastInsertRowid($r)

Object Iterators

SQLite takes advantage of a new PHP 5 feature that lets you access rows from your database query as though they’re just elements from an array. This feature is called iteration and is the subject of Chapter 6.

Don’t confuse this with sqlite_array_query( ). SQLite is not prefetching all the rows and storing them as keys inside an array; instead, upon each loop iteration, it returns a new row as if the row already lived in your results array:

// one at a time
$r = $db->query('SELECT * FROM users');
foreach ($r as $row) {
    // do something with $row
}

You can also embed the query directly inside the foreach:

// one at a time
foreach ($db->query('SELECT * FROM users') as $row) {
    // do something with $row
}

While this interface hides many of the messy details of database result retrieval, SQLite must still make the requests and transfer the data from the database. Therefore, this syntax works only in foreach. You cannot use a for loop or pass $db->query( ) into other array functions, such as array_map( ).

When iterating over an SQLite result, it’s usually best to use the unbuffered_query( ) function or unbufferedQuery( ) method instead of the simple query( ) method. Since you rarely take advantage of the additional benefits provided by query( ), unbuffered_query( ) gives you an efficiency gain at no cost.

// one at a time
$r = $db->unbufferedQuery('SELECT * FROM users');
foreach ($r as $row) {
    // do something with $row
}