Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 22

As of PHP 5, support was added for the open source database server SQLite (http://www.sqlite. org/). This was done partly in response to the decision to unbundle MySQL from version 5 due to
licensing discrepancies and partly due to a realization that users might benefit from the availability of another powerful database that nonetheless requires measurably less configuration and mainte- nance as compared to similar products. This chapter introduces both SQLite and PHP’s ability to interface with this surprisingly capable database engine.

Introduction to SQLite

SQLite is a very compact, multiplatform SQL database engine written in C. Practically SQL-92 compliant, SQLite offers many of the core management features made available by products such as MySQL, Oracle, and PostgreSQL, yet at considerable savings in terms of cost, learning curve, and administra- tion investment. Some of SQLite’s more compelling characteristics include the following:

• SQLite stores an entire database in a single file, allowing for easy backup and transfer.

• SQLite’s approach to database security is based entirely on the executing user’s file permis- sions. So, for example, user web might own the Web server daemon process and, through a script executed on that server, attempt to write to an SQLite database named mydatabase.db. Whether this user is capable of doing so depends entirely on the mydatabase.db file permissions.
• SQLite offers default transactional support, automatically integrating commit and rollback support.
• SQLite is available under a public domain license (it’s free) for both the Microsoft Windows and Linux platforms.

This section offers a brief guide to the SQLite command-line interface. The purpose of this section is twofold. First, it provides an introductory look at this useful client. Second, the steps demonstrated create the data that will serve as the basis for all subsequent examples in this chapter.

Installing SQLite

When PHP 5.0 was released, support for SQLite was added and the extension was enabled by default. Therefore, if you’re running PHP 5.0.X, you can begin using SQLite without performing any addi- tional steps.

407

As of PHP 5.1 this changed in two ways: while the extension continues to be bundled with the language, it is left to the user to decide whether it will be enabled. Further, as of PHP 5.1, SQLite support is handled through the PDO extension (introduced in Chapter 23). Therefore if you’re running PHP 5.1 or greater, you’ll need to add the following two lines to the php.ini file in this order:
extension=php_pdo.dll extension=php_sqlite.dll

There is one related utility omitted from the PHP distribution, namely sqlite, a command-line interface to the engine. Because this utility is quite useful (although not necessary), consider installing the SQLite library from http://www.sqlite.org/, which includes this utility. Then configure (or reconfigure) PHP with the --with-sqlite=/path/to/library flag. The next section shows you how to use this interface.
Windows users will need to download the SQLite extension from http://snaps.php.net/win32/ PECL_STABLE/php_sqlite.dll. Once downloaded, place this DLL file within the same directory as the others (PHP-INSTALL-DIR\ext) and add the following line to your php.ini file:

php_extension=php_sqlite.dll

Using the SQLite Command-Line Interface

The SQLite command-line interface offers a simple means for interacting with the SQLite database server. With this tool, you can create and maintain databases, execute administrative processes such as backups and scripts, and tweak the client’s behavior. Begin by opening a terminal window and executing SQLite with the help option:

%>sqlite -help

If you’ve downloaded SQLite version 3 for Windows, you need to execute it like so:

%>sqlite3 -help

In either case, before exiting back to the command line, you’ll be greeted with the command’s usage syntax and a menu consisting of numerous options. Note that the usage syntax specifies that a file name is required to enter the SQLite interface. This file name is actually the name of the data- base. When supplied, a connection to this database will be opened if the executing user possesses adequate permissions. If the supplied database does not exist, it will be created, again if the executing user possesses the necessary privileges.
As an example, create a database named corporate.db. This database consists of a single table,
employees. In this section, you’ll learn how to use SQLite’s command-line program to create the database, table, and sample data. Although this section isn’t intended as a replacement for the docu- mentation, it should be sufficient to enable you to familiarize yourself with the very basic aspects of SQLite and its command-line interface.

1. Open a new SQLite database, as follows. Because this database presumably doesn’t already exist, the mere act of opening a nonexistent database will first result in its creation:

%>sqlite corporate.db

2. Create a table:

sqlite>create table employees (
...>empid integer primary key,
...>name varchar(25),
...>title varchar(25));

3. Check the table structure for accuracy:

sqlite>.schema employees

Note that a period (.) prefaces the schema command. This syntax requirement holds true for all commands found under the help menu.
4. Insert a few data rows:

sqlite> insert into employees values(NULL,"Jason Gilmore","Chief Slacker"); sqlite> insert into employees values(NULL,"Sam Spade","Technologist"); sqlite> insert into employees values(NULL,"Ray Fox","Comedian");

5. Query the table, just to ensure that all is correct:

sqlite>select * from employees;

You should see the following:

1|Jason Gilmore|Chief Slacker
2|Sam Spade|Technologist
3|Ray Fox|Comedian

6. Quit the interface with the following command:

sqlite>.quit

■Note PHP 5.X is bundled with SQLite version 2; however, SQLite version 3 has been out for quite some time.
Therefore, if you wish to use the SQLite command-line interface to create a database and then move it to a location for interaction with a PHP script, be sure to have downloaded SQLite version 2 because the database file formats between these two versions are incompatible. Alternatively, you can convert SQLite 2.X databases to a version 3 format by executing the following command: sqlite2 original.db .dump | sqlite3 new.db. Note that you’ll need both the version 2 and version 3 interfaces to execute this command. Also, your interface names might not include the 2 or the 3; I’ve only done so to clarify which interface should be referenced where.

PHP’s SQLite Library

The SQLite functions introduced in this section are quite similar to those found in the other PHP- supported database libraries such as Oracle or MySQL. In fact, for many of the functions, the name is the only real differentiating factor. Therefore, if you have experience using any relational data- base, picking up SQLite should be a snap. Even if you’re entirely new to the concept, don’t worry; you’ll likely find that these functions are quite easy to use.

sqlite.assoc_case = 0 | 1 | 2

Scope: PHP_INI_ALL; Default value: 0
One PHP configuration directive is pertinent to SQLite: sqlite.assoc_case, which determines the case used for retrieved column names. While SQLite is case insensitive when it comes to dealing with column names, various other database servers attempt to standardize name formats by always returning them in uppercase letters. This dichotomy can be problematic when porting an application to

SQLite because the column names used in the application may be standardized in uppercase to account for the database server’s tendencies. To modify this behavior, you can use the sqlite.assoc_case direc- tive. By default, this directive is set to 0, which retains the case used in the table definitions. If it’s set to 1, the names will be converted to uppercase. If it’s set to 2, the names will be converted to lowercase.

Opening a Connection

Before you can retrieve or manipulate any data located in an SQLite database, you must first estab- lish a connection. Two functions are available for doing so, sqlite_open() and sqlite_popen().

Opening an SQLite Database

The sqlite_open() function opens an SQLite database, first creating the database if it doesn’t already exist. Its prototype follows:

resource sqlite_open(string filename [, int mode [, string &error_message]])

The filename parameter specifies the database name. The optional mode parameter determines the access privilege level under which the database will be opened and is specified as an octal value (the default is 0666) as might be used to specify modes in Unix. Currently, this parameter is unsup- ported by the API. The optional error_message parameter is actually automatically assigned a value specifying an error if the database cannot be opened. If the database is successfully opened, the function returns a resource handle pointing to that database.
Consider an example:

<?php
$sqldb = sqlite_open("/home/book/22/corporate.db")
or die("Could not connect!");
?>

This either opens an existing database named corporate.db, creates a database named
corporate.db within the directory /home/book/22/, or results in an error, likely because of privilege problems. If you experience problems creating or opening the database, be sure that the user owning the Web server process possesses adequate permissions for writing to this directory.

Opening a Persistent SQLite Connect

The function sqlite_popen() operates identically to sqlite_open() except that it uses PHP’s persis- tent connection feature in an effort to conserve resources. Its prototype follows:

resource sqlite_popen(string filename [, int mode [, string &error_message]])

The function first verifies whether a connection already exists. If it does, it reuses this connection; otherwise, it creates a new one. Because of the performance improvements offered by this function, you should use sqlite_popen() instead of sqlite_open().

OBJECT-ORIENTED SQLITE

Although this chapter introduces PHP’s SQLite library using the procedural approach, an object-oriented interface is also supported. All functions introduced in this chapter are also supported as methods when using the object-oriented interface. However, the names differ slightly in that the sqlite_ prefix is removed from them. Therefore, the only significant usage deviation is in regard to referencing the methods by way of an object ($objectname->methodname()) rather than by passing around a resource handle. Also, the constructor takes the place of the sqlite_open() function, negating the need to specifically open a database connection. The class is instantiated by calling the constructor like so:

$sqldb = new SQLiteDatabase(string databasename [, int mode
[, string &error_message]]);

Once the object is created, you can call methods just as you do for any other class. For example, you can execute a query and determine the number of rows returned with the following code:

$sqldb = new SQLiteDatabase("corporate.db");
$sqldb->query("SELECT * FROM employees");
echo $sqldb->numRows()." rows returned.";

See the PHP manual (http://www.php.net/sqlite) for a complete listing of the available methods.

Creating a Table in Memory

Sometimes your application may require database access performance surpassing even that offered by SQLite’s default behavior, which is to manage databases in self-contained files. To satisfy such requirements, SQLite supports the creation of in-memory (RAM-based) databases, accomplished by calling sqlite_open() like so:

$sqldb = sqlite_open(":memory:");

Once open, you can create a table that will reside in memory by calling sqlite_query(), passing in a CREATE TABLE statement. Keep in mind that such tables are volatile, disappearing once the script has finished executing.

Closing a Connection

Good programming practice dictates that you close pointers to resources once you’re finished with them. This maxim holds true for SQLite; once you’ve completed working with a database, you should close the open handle. One function, sqlite_close(), accomplishes just this. Its prototype follows:

void sqlite_close(resource dbh)

You should call this function after all necessary tasks involving the database have been completed. An example follows:
<?php
$sqldb = sqlite_open("corporate.db");
// Perform necessary tasks sqlite_close($sqldb);
?>

Note that if a pending transaction has not been completed at the time of closure, the transaction
will automatically be rolled back.

Querying a Database

The majority of your time spent interacting with a database server takes the form of SQL queries. The functions sqlite_query() and sqlite_unbuffered_query() offer the main vehicles for submitting these queries to SQLite and returning the subsequent result sets. You should pay particular attention to the specific advantages of each because applying them inappropriately can negatively impact performance and capabilities.

Executing a SQL Query

The sqlite_query() function executes a SQL query against the database. Its prototype follows:

resource sqlite_query(resource dbh, string query [, int result_type
[, string &error_msg]])

If the query is intended to return a result set, FALSE is returned if the query fails. All other queries return TRUE if the query is successful.
If the query is intended to return a result set, the optional result_type parameter specifies how the result set is indexed. By default it will return the set using both associative and numerical indices (SQLITE_BOTH). You can use SQLITE_ASSOC to return the set as associative indices, and SQLITE_NUM to return the set using numerical indices.
Finally, the optional &error_msg parameter (available as of PHP 5.1.0) can be used should you wish to review any SQL syntax error that might occur. Should an error occur, the error message will be made available by way of a variable of the parameter name.
An example follows:

<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb, "SELECT * FROM employees", SQLITE_NUM, &error) OR DIE($error);
while (list($empid, $name) = sqlite_fetch_array($results)) {
echo "Name: $name (Employee ID: $empid) <br />";
}
sqlite_close($sqldb);
?>

This yields the following results:

Name: Jason Gilmore (Employee ID: 1) Name: Sam Spade (Employee ID: 2) Name: Ray Fox (Employee ID: 3)

Keep in mind that sqlite_query() will only execute the query and return a result set (if one is warranted); it will not output or offer any additional information regarding the returned data. To obtain such information, you need to pass the result set into one or several other functions, all of which are introduced in the following sections. Furthermore, sqlite_query() is not limited to executing SELECT queries. You can use this function to execute any supported SQL-92 query.

Executing an Unbuffered SQL Query

The sqlite_unbuffered_query() function can be thought of as an optimized version of sqlite_query(), identical in every way except that it returns the result set in a format intended to be used in the order in which it is returned, without any need to search or navigate it in any other way. Its prototype follows:

resource sqlite_unbuffered_query(resource dbh, string query [, int result_type
[, string &error_msg]])

This function is particularly useful if you’re solely interested in dumping a result set to output, an HTML table or a text file, for example.
The optional result_type and &error_msg parameters operate identically to those introduced in the previous section on sqlite_query().
Because this function is optimized for returning result sets intended to be output in a straight- forward fashion, you cannot pass its output to functions such as sqlite_num_rows(), sqlite_seek(), or any other function with the purpose of examining or modifying the output or output pointers. If you require the use of such functions, use sqlite_query() to retrieve the result set instead.

Retrieving the Most Recently Inserted Row Identifier

It’s common to reference a newly inserted row immediately after the insertion is completed, which in many cases is accomplished by referencing the row’s autoincrement field. Because this value will contain the highest integer value for the field, determining it is as simple as searching for the column’s maximum value. The sqlite_last_insert_rowid() function accomplishes this for you, returning that value. Its prototype follows:

int sqlite_last_insert_rowid(resource dbh)

Parsing Result Sets

Once a result set has been returned, you’ll likely want to do something with the data. The functions in this section demonstrate the many ways that you can parse the result set.

Returning the Result Set as an Associative Array

The sqlite_fetch_array() function returns an associative array consisting of the items found in the result set’s next available row, or returns FALSE if no more rows are available. Its prototype follows:

array sqlite_fetch_array(resource result [, int result_type [, bool decode_binary])

The optional result_type parameter can be used to specify whether the columns found in the result set row should be referenced by their integer-based position in the row or by their actual name. Specifying SQLITE_NUM enables the former, while SQLITE_ASSOC enables the latter. You can return both referential indexes by specifying SQLITE_BOTH. Finally, the optional decode_binary parameter determines whether PHP will decode the binary-encoded target data that had been previously encoded using the function sqlite_escape_string(). This function is introduced in the later section “Working with Binary Data.”

■Tip If SQLITE_ASSOC or SQLITE_BOTH are used, PHP will look to the sqlite.assoc_case configuration directive to determine the case of the characters.

Consider an example:

<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb, "SELECT * FROM employees");
while ($row = sqlite_fetch_array($results,SQLITE_BOTH)) {
echo "Name: $row[1] (Employee ID: ".$row['empid'].")<br />";
}
sqlite_close($sqldb);
?>

This returns the following:

Name: Jason Gilmore (Employee ID: 1) Name: Sam Spade (Employee ID: 2) Name: Ray Fox (Employee ID: 3)

Note that the SQLITE_BOTH option was used so that the returned columns could be referenced both by their numerically indexed position and by their name. Although it’s not entirely practical, this example serves as an ideal means for demonstrating the function’s flexibility.
One great way to render your code a tad more readable is to use PHP’s list() function in conjunc- tion with sql_fetch_array(). With it, you can both return and parse the array into the required components all on the same line. Let’s revise the previous example to take this idea into account:
<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb, "SELECT * FROM employees");
while (list($empid, $name) = sqlite_fetch_array($results)) {
echo "Name: $name (Employee ID: $empid)<br />";
}
sqlite_close($sqldb);
?>

Consolidating sqlite_query() and sqlite_fetch_array()

The sqlite_array_query() function consolidates the capabilities of sqlite_query() and sqlite_ fetch_array() into a single function call, both executing the query and returning the result set as an array. Its prototype follows:
array sqlite_array_query(resource dbh, string query [, int res_type
[, bool decode_binary]])

The input parameters work exactly like those introduced in the component functions sqlite_query() and sqlite_fetch_array(). According to the PHP manual, this function should only be used for retrieving result sets of fewer than 45 rows. However, in instances where 45 or fewer rows are involved, this function provides both a considerable improvement in performance and, in certain cases, a slight reduction in total lines of code. Consider an example:

<?php
$sqldb = sqlite_open("corporate.db");
$rows = sqlite_array_query($sqldb, "SELECT empid, name FROM employees");
foreach ($rows AS $row) {
echo $row["name"]." (Employee ID: ".$row["empid"].")<br />";
}
sqlite_close($sqldb);
?>

This returns the following:

Jason Gilmore (Employee ID: 1) Sam Spade (Employee ID: 2)
Ray Fox (Employee ID: 3)

Retrieving Select Result Set Columns

The sqlite_column() function is useful if you’re interested in just a single column from a given result row or set. Its prototype follows:

mixed sqlite_column(resource result, mixed index_or_name [, bool decode_binary])

You can retrieve the column either by name or by index offset. Finally, the optional decode_binary parameter determines whether PHP will decode the binary-encoded target data that had been previously encoded using the function sqlite_escape_string(). This function is introduced in the later section “Working with Binary Data.”
For example, suppose you retrieved all rows from the employee table. Using this function, you could selectively poll columns, like so:

<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb,"SELECT * FROM employees WHERE empid = '1'");
$name = sqlite_column($results,"name");
$empid = sqlite_column($results,"empid");
echo "Name: $name (Employee ID: $empid) <br />";
sqlite_close($sqldb);
?>

This returns the following:

Name: Jason Gilmore (Employee ID: 1)

Ideally, you’ll want to use this function when you’re working either with result sets consisting of numerous columns or with particularly large columns.

Retrieving the First Column in the Result Set

The sqlite_fetch_single() function operates identically to sql_fetch_array() except that it returns just the value located in the first column of the result set. Its prototype follows:

string sqlite_fetch_single(resource row_set [, int result_type
[, bool decode_binary]])

■Tip This function has an alias: sqlite_fetch_string(). Except for the name, it’s identical in every way.

Consider an example. Suppose you’re interested in querying the database for a single column. To reduce otherwise unnecessary overhead, you should opt to use sqlite_fetch_single() over sqlite_fetch_array(), like so:
<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb,"SELECT name FROM employees WHERE empid < 3");
while ($name = sqlite_fetch_single($results)) {
echo "Employee: $name <br />";
}
sqlite_close($sqldb);
?>

This returns the following:

Employee: Jason Gilmore
Employee: Sam Spade

Retrieving Result Set Details

You’ll often want to learn more about a result set than just its contents. Several SQLite-specific functions are available for determining information such as the returned field names, the number of fields and rows returned, and the number of rows changed by the most recent statement. These functions are introduced in this section.

Retrieving Field Names

The sqlite_field_name() function returns the name of the field located at a desired index offset found in the result set. Its prototype follows:

string sqlite_field_name(resource result, int field_index)

<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb,"SELECT * FROM employees");
echo "Field name found at offset #0: ".sqlite_field_name($results,0)."<br />"; echo "Field name found at offset #1: ".sqlite_field_name($results,1)."<br />"; echo "Field name found at offset #2: ".sqlite_field_name($results,2)."<br />"; sqlite_close($sqldb);
?>

This returns the following:

Field name found at offset #0: empid Field name found at offset #1: name Field name found at offset #2: title

As is the case with all numerically indexed arrays, the offset starts at 0, not 1.

Retrieving the Number of Columns in the Result Set

The sqlite_num_fields() function returns the number of columns located in the result set. Its prototype follows:

int sqlite_num_fields(resource result_set)

An example follows:

<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb, "SELECT * FROM employees");
echo "Total fields returned: ".sqlite_num_fields($results)."<br />";
sqlite_close($sqldb);
?>

This returns the following:

Total fields returned: 3

Retrieving the Number of Rows in the Result Set

The sqlite_num_rows() function returns the number of rows located in the result set. Its prototype follows:

int sqlite_num_rows(resource result_set)

An example follows:

<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb, "SELECT * FROM employees");
echo "Total rows returned: ".sqlite_num_rows($results)."<br />";
sqlite_close($sqldb);
?>

This returns the following:

Total rows returned: 3

Retrieving the Number of Affected Rows

The sqlite_changes() function returns the total number of rows affected by the most recent modi- fication query. Its prototype follows:

int sqlite_changes(resource dbh)

For instance, if an UPDATE query modifies a field located in 12 rows, executing this function following that query would return 12.

Manipulating the Result Set Pointer

Although SQLite is indeed a database server, in many ways it behaves much like what you experience when working with file I/O. One such way involves the ability to move the row “pointer” around the result set. Several functions are offered for doing just this, all of which are introduced in this section.

Retrieving the Row Residing at the Current Pointer Position

The sqlite_current() function is identical to sqlite_fetch_array() in every way except that it does not advance the pointer to the next row of the result set. Instead, it only returns the row residing at the current pointer position. If the pointer already resides at the end of the result set, FALSE is returned. Its prototype follows:

array sqlite_current(resource result [, int result_type [, bool decode_binary]])

Determining Whether the End of a Result Set Has Been Reached

The sqlite_has_more() function determines whether the end of the result set has been reached, returning TRUE if additional rows are still available, and FALSE otherwise. Its prototype follows:

boolean sqlite_has_more(resource result_set)

An example follows:

<?php
$sqldb = sqlite_open("mydatabase.db");
$results = sqlite_query($sqldb, "SELECT * FROM employee");
while ($row = sqlite_fetch_array($results,SQLITE_BOTH)) {
echo "Name: $row[1] (Employee ID: ".$row['empid'].")<br />";
if (sqlite_has_more($results)) echo "Still more rows to go!<br />";
else echo "No more rows!<br />";
}
sqlite_close($sqldb);
?>

This returns the following:

Name: Jason Gilmore (Employee ID: 1) Still more rows to go!
Name: Sam Spade (Employee ID: 2)
Still more rows to go!
Name: Ray Fox (Employee ID: 3) No more rows!

Moving the Result Set Pointer Forward

The sqlite_next() function moves the result set pointer to the next position, returning TRUE on success and FALSE if the pointer already resides at the end of the result set. Its prototype follows:

boolean sqlite_next(resource result)

Moving the Result Set Pointer Backward

The sqlite_rewind() function moves the result set pointer back to the first row, returning FALSE if no rows exist in the result set and TRUE otherwise. Its prototype follows:

boolean sqlite_rewind(resource result)

Moving the Result Set Pointer to a Desired Location

The sqlite_seek() function moves the pointer to a desired row number, returning TRUE if the row exists and FALSE otherwise. Its prototype follows:

boolean sqlite_seek(resource result, int row_number)

Consider an example in which an employee of the month will be randomly selected from a result set consisting of the entire staff:
<?php
$sqldb = sqlite_open("corporate.db");
$results = sqlite_query($sqldb, "SELECT empid, name FROM employees");

// Choose a random number found within the range of total returned rows
$random = rand(0,sqlite_num_rows($results)-1);

// Move the pointer to the row specified by the random number sqlite_seek($results, $random);

// Retrieve the employee ID and name found at this row list($empid, $name) = sqlite_current($results);
echo "Randomly chosen employee of the month: $name (Employee ID: $empid)";
sqlite_close($sqldb);
?>

This returns the following (this shows only one of three possible outcomes):

Randomly chosen employee of the month: Ray Fox (Employee ID: 3)

One point of common confusion that arises in this example regards the starting index offset of result sets. The offset always begins with 0, not 1, which is why you need to subtract 1 from the total rows returned in this example. As a result, the randomly generated row offset integer must fall within a range of 0 and one less than the total number of returned rows.

Retrieving a Table’s Column Types

The function sqlite_fetch_column_types() returns an array consisting of the column types located in a table. Its prototype follows:

array sqlite_fetch_column_types(string table, resource dbh)

The returned array includes both the associative and numerical hash indices. The following example outputs an array of column types located in the employee table used earlier in this chapter:

<?php
$sqldb = sqlite_open("corporate.db");
$columnTypes = sqlite_fetch_column_types("employees", $sqldb);
print_r($columnTypes);
sqlite_close($sqldb);
?>

This example returns the following (formatted for readability):

Array (
[empid] => integer [name] => varchar(25) [title] => varchar(25)
)

Working with Binary Data

SQLite is capable of storing binary information in a table, such as a GIF or a JPEG image, a PDF docu- ment, or a Microsoft Word document. However, unless you treat this data carefully, errors in both storage and communication could arise. Several functions are available for carrying out the tasks necessary for managing this data, one of which is introduced in this section. The other two relevant functions are introduced in the next section.
Some characters or character sequences have special meaning to a database, and therefore they must be treated with special care when being inserted into a table. For example, SQLite expects that single quotes signal the delimitation of a string. However, because this character is often used within data that you might want to include in a table column, a means is required for tricking the database server into ignoring single quotes on these occasions. This is commonly referred to as escaping these special characters, often done by prefacing the special character with some other character, a single quote ('), for example. Although you can do this manually, a function is available that will do the job for you. The sqlite_escape_string() function escapes any single quotes and other binary-unsafe characters intended for insertion in an SQLite table. Its prototype follows:

string sqlite_escape_string(string item)

Let’s use this function to escape an otherwise invalid query string:

<?php
$str = "As they always say, this is 'an' example.";
echo sqlite_escape_string($str);
?>

This returns the following:

As they always say, this is ''an'' example.

If the string contains a NULL character or begins with 0x01, circumstances that have special meaning when working with binary data, sqlite_escape_string() will take the steps necessary to properly encode the information so that it can be safely stored and later retrieved.

■Note The NULL character typically signals the end of a binary string, while 0x01 is the escape character used within binary data. Therefore, to ensure that the escape character is properly interpreted by the binary data parser, it needs to be decoded.

When you’re using user-defined functions, a topic discussed in the next section, you should never use this function. Instead, use the sqlite_udf_encode_binary() and sqlite_udf_decode_ binary() functions. Both are introduced in the next section.

Creating and Overriding SQLite Functions

An intelligent programmer will take every opportunity to reuse code. Because many database-driven applications often require the use of a core task set, there are ample opportunities to reuse code. Such tasks often seek to manipulate database data, producing some sort of outcome based on the retrieved data. As a result, it would be quite convenient if the task results could be directly returned via the SQL query, like so:

sqlite>SELECT convert_salary_to_gold(salary)
...> FROM employee WHERE empID=1";

PHP’s SQLite library offers a means for registering and maintaining customized functions such as this. This section shows you how it’s accomplished.

Creating an SQLite Function

The sqlite_create_function() function enables you to register custom PHP functions as SQLite user-defined functions (UDFs). Its prototype follows:

boolean sqlite_create_function(resource dbh, string func, mixed callback
[, int num_args])

For example, this function would be used to register the convert_salary_to_gold() function discussed in the opening paragraphs of this section, like so:
<?php
// Define gold's current price-per-ounce define("PPO",400);

// Calculate how much gold an employee can purchase with salary function convert_salary_to_gold($salary)
{
return $salary / PPO;
}

// Connect to the SQLite database
$sqldb = sqlite_open("corporate.db");

// Create the user-defined function
sqlite_create_function($sqldb, "salarytogold", "convert_salary_to_gold", 1);

// Query the database using the UDF
$query = "select salarytogold(salary) FROM employees WHERE empid=1";
$result = sqlite_query($sqldb, $query);
list($salaryToGold) = sqlite_fetch_array($result);

// Display the results
echo "The employee can purchase: ".$salaryToGold." ounces.";

// End the database connection sqlite_close($sqldb);
?>

Assuming employee Jason makes $10,000 per year, you can expect the following output:

The employee can purchase 25 ounces.

Encoding Binary Data

The sqlite_udf_encode_binary() function encodes any binary data intended for storage within an
SQLite table. Its prototype follows:

string sqlite_udf_encode_binary(string data)

Use this function instead of sqlite_escape_string() when you’re working with data sent to a
UDF.

Decoding Binary Data

The sqlite_udf_decode_binary() function decodes any binary data previously encoded with the
sqlite_udf_encode_binary() function. Its prototype follows:

string sqlite_udf_decode_binary(string data)

Use this function when you’re returning possibly binary-unsafe data from a UDF.

Creating Aggregate Functions

When you work with database-driven applications, it’s often useful to derive some value based on some collective calculation of all values found within a particular column or set of columns. Several such functions are commonly made available within a SQL server’s core functionality set. A few such commonly implemented functions, known as aggregate functions, include sum(), max(), and min(). However, you might require a custom aggregate function not otherwise available within the server’s default capabilities. SQLite compensates for this by offering the ability to create your own. The function used to register your custom aggregate functions is sqlite_create_aggregate(). Its prototype follows:

boolean sqlite_create_aggregate(resource dbh, string func, mixed step_func, mixed final_func [, int num_args])

Actually it registers two functions: step_func, which is called on every row of the query target, and final_func, which is used to return the aggregate value back to the caller. Once registered, you can call final_func within the caller by the alias func. The optional num_args parameter specifies the number of parameters the aggregate function should take. Although the SQLite parser attempts to discern the number if this parameter is omitted, you should always include it for clarity’s sake.

Consider an example. Building on the salary conversion example from the previous section, suppose you want to calculate the total amount of gold employees could collectively purchase:

<?php
// Define gold's current price-per-ounce define("PPO",400);

// Create the aggregate function function total_salary(&$total,$salary)
{
$total += $salary;
}

// Create the aggregate finalization function function convert_to_gold(&$total)
{
return $total / PPO;
}

// Connect to the SQLite database
$sqldb = sqlite_open("corporate.db");

// Register the aggregate function
sqlite_create_aggregate($sqldb, "computetotalgold", "total_salary", "convert_to_gold",1);

// Query the database using the UDF
$query = "select computetotalgold(salary) FROM employees";
$result = sqlite_query($sqldb, $query);
list($salaryToGold) = sqlite_fetch_array($result);

// Display the results
echo "The employees can purchase: ".$salaryToGold." ounces.";

// End the database connection sqlite_close($sqldb);
?>

If your employees’ salaries total $16,000, you could expect the following output:

The employees can purchase 40 ounces.

Summary

The administrative overhead required of many database servers often outweighs the advantages of the added power they offer to many projects. SQLite offers an ideal remedy to this dilemma, providing a fast and capable back end at a cost of minimum maintenance. Given SQLite’s commitment to standards, ideal licensing arrangements, and quality, consider saving yourself time, resources, and money by using SQLite for your future projects.