Basic SQLite Functionality

Now that the design differences between SQLite and MySQL (as well as other RDBMS packages) have been introduced, you are ready to use SQLite from within PHP. Although SQLite is a unique RDBMS from a design standpoint, it shares much in common with many common databases, such as MySQL, from a PHP development standpoint. As was the case with MySQL in Chapter 24, a few operations are common to every PHP script using SQLite. Following is an outline of these common operations:

• Open or create a SQLite database.
  
  
• Perform the desired SQL queries.
  
  
• Retrieve any data returned from the queries.
  
  
• Close the database.
  
  

The following sections outline the functions that perform these operations and their use.

Opening and Closing Databases

The first task when you're working with SQLite is to open the database. "Opening" a database in SQLite is synonymous with the creation or opening of the database file in the file system. This implies that if the database does not exist, the appropriate Unix write permissions must be set for the database file to be created. The creation or opening of a database is done using one of two functions: sqlite_open() or sqlite_popen(). Both functions behave in an identical manner; the single difference is that sqlite_popen() will keep the database connection open at the end of a request (persistent connection), whereas sqlite_open() will close it. Because under most circumstances in a Web environment, persistent connections are preferred, the sqlite_popen() function will be used in our examples. The syntax for the sqlite_popen() function is as follows:

 sqlite_popen($filename [, $mode [, &$err_message]]); 

$filename is the path and filename of the database to open, $mode is the permission mask to open the file as, and the $err_message parameter is a reference to a variable in which to store the error message (if an error occurs).

NOTE

Currently, the $mode parameter is not used by the SQLite library. In the future, it will be used to determine the permissions under which the specified SQLite database is intended to be used.

When the sqlite_popen() function is executed, SQLite will attempt to open or create the database specified by $filename. If for any reason this operation fails, the variable referenced by the $err_message parameter will contain a string describing the error that occurred, and sqlite_popen() will return false. If the database was created or opened successfully, sqlite_popen() will return a resource representing the connection to the database.

Listing 25.1 provides an example of opening a database connection to a SQLite database:

Listing 25.1. Opening a SQLite Database

 <?php      $err_msg = "";      $db_conn = sqlite_popen("/tmp/mydatabase", 0666, &$err_msg);      if(!$db_conn) {           trigger_error("Could not open database: (Reason: $err_msg)");      }      /* Perform database operations */ ?> 

Although generally, SQLite databases are a form of permanent storage, they can also be used for temporary processing of large data sets for complex analysis using SQL by creating a SQLite database in memory. To create a SQLite database in memory, pass the string ':memory:' for the $filename parameter of either sqlite_open() or sqlite_popen() functions.

NOTE

Tables created in memory will be destroyed at the end of the current request, and therefore cannot be persistent. Thus, when you are creating tables in memory, also called temporary tables, sqlite_open() and sqlite_popen() behave identically.

When databases are opened using sqlite_open(), they should be closed when no longer needed. Although PHP will automatically close open nonpersistent database connections, they can also be closed manually using the sqlite_close() function, whose syntax is shown next:

 sqlite_close($db); 

$db is a valid database handle resource as returned by a call to sqlite_open(). Using sqlite_close() on a connection that was opened as persistent using sqlite_popen() will also close the connection, removing its persistent status.

Performing Queries

After a SQLite database has been opened, queries can be performed against it using a number of available functions. The most basic of all SQLite query functions is the sqlite_query() function, which has the following syntax:

 sqlite_query($db, $query); 

$db is a valid database handle resource and $query is a SQL query to execute. As is the case with MySQL, queries passed to SQLite should not be terminated using a semicolon. When executed, the sqlite_query() function will attempt to execute the provided query and, if successful, return a resource representing the results of that query. If for whatever reason the query failed, sqlite_query() will return false instead. Listing 25.2 details the use of the sqlite_query() function to first create a table and then insert some data into it:

Listing 25.2. Using the sqlite_query() Function

 <?php      $db = sqlite_open(":memory:");      if(!$db) die("Could not create the temporary database");      $query = "CREATE TABLE cities(name VARCHAR(255), state VARCHAR(2))";      sqlite_query($db, $query);           $cities[] = array('name' => 'Chicago',                             'state'=> 'IL');           $cities[] = array('name' => 'Pentwater',                             'state' => 'MI');           $cities[] = array('name' => 'Flint',                             'state'=> 'MI');      foreach($cities as $city) {          $query = "INSERT INTO cities VALUES(" .                                "'{$city['name']}', '{$city['state']}')";          if(!sqlite_query($db, $query)) {           trigger_error("Could not insert city " .                                              "'{$city['name']}, {$city['state']}");          }      }           sqlite_close($db); ?> 

Complementing the sqlite_query() function is the sqlite_unbuffered_query() function. When a query is performed using sqlite_query(), the entire resultset of that query is copied into memory. This is required when your scripts need to access the entire resultset using functions such as sqlite_seek() or functions that require the entire resultset to work, such as sqlite_num_rows() (both discussed later). However, as often is the case, many resultsets are accessed only sequentially one row at a timethis is where sqlite_unbuffered_query() is used. Because this function does not copy the entire resultset to memory, the reduced functionality is traded for faster query execution.

As is the case with all SQL databases, when you're performing queries based on user data, it becomes necessary to escape certain characters that have special meaning to SQL. In SQLite, characters can be escaped using the sqlite_escape_string() function. The syntax of this function is as follows:

 sqlite_escape_string($string); 

$string is the string to escape. As expected, the sqlite_escape_string() function will return a copy of the string appropriately escaped and ready to be used within a SQL query. To avoid both security and functional issues, all data that comes from an untrusted outside source should be escaped prior to being used within a query.

Retrieving Results

I have discussed the differences between buffered and unbuffered queries using sqlite_query() and sqlite_unbuffered_query(); however, I have yet to discuss how to retrieve the data from either type of resultset. In SQLite, retrieving results is done in a similar manner to other relational database extensions such as MySQL. The following section describes the different methods by which resultset data can be accessed from within PHP.

Returning Rows as Arrays Sequentially

The most basic method of retrieving a resultset is sequentially using the sqlite_fetch_array() function. The syntax for sqlite_fetch_array() is as follows:

 sqlite_fetch_array($db [, $res_type [, $decode]]); 

$db represents the database handle resource, $res_type is the type of array to create, and $decode is a Boolean indicating whether the results should be automatically decoded before being placed into the array. When executed, this function will return the next row in the resultset as an array or false if there are no more rows available. The type of array that is returned by sqlite_fetch_array() is determined by the $res_type parameter and is one of the following constant values:

SQLITE_ASSOC	Returns an associative array containing column/value pairs for the row.
SQLITE_NUM	Returns an indexed array of row values.
SQLITE_BOTH	Returns an array containing both associative and numeric keys for the current row values.

By default, sqlite_fetch_array() returns both numeric and associative keys for each column within the current row (SQLITE_BOTH).

NOTE

The case of the key values for associative arrays returned by sqlite_fetch_array() is determined by the sqlite.assoc_case configuration directive.

The third and final parameter that may be passed to the sqlite_fetch_array() function is the $decode parameter. This parameter is a Boolean value indicating whether values returned in the array for the current row should be decoded automatically using the sqlite_escape_string() function. By default, sqlite_fetch_array() will decode values within the resultset, and this parameter should be changed only in special circumstances when a nondecoded version of the data is required.

Returning Entire Resultsets as an Array

At times, many developers tend to copy an entire resultset into an array using a script similar to that found in Listing 25.3 (assume $result is a valid result resource):

Listing 25.3. Copying Resultsets into an Array Manually

 <?php      $rows = array();      while($row = sqlite_fetch_array($result, $res_type, $decode)) {         $rows[] = $row;      } ?> 

As an alternative to this PHP code, SQLite provides a single function that performs a query and returns an array containing the entire resultset identical to that produced by Listing 25.3the sqlite_array_query() function. The syntax for this function is as follows:

 sqlite_array_query($db, $query [, $res_type [, $decode]]) 

$db is the database handle resource, and $query is the query to execute. The two optional parameters, $res_type and $decode, as expected, have the same meaning as described for the sqlite_fetch_array() function. When executed, the sqlite_array_query() function will execute the provided query and return an array containing the entire resultset constructed in the same method as the code provided in Listing 25.3. The sqlite_array_query() function should always be used instead of a solution similar to that in Listing 25.3. Not only is the code cleaner, it is also substantially faster.

Returning a Single Value

Another common operation that is done when working with SQL tables is to access a single column and row within a particular table, such as that returned by the following query:

 SELECT value FROM settings WHERE name='foo' LIMIT 1 

Because the preceding query can return only a single column and row, using mysql_fetch_array() is unnecessary. Rather, for situations such as this, SQLite provides the sqlite_fetch_single() function.

 sqlite_fetch_single($result); 

This function accepts a single parameter (a valid resultset resource $result) and returns a string representing the first column of the first row in the resultset, or false if the resultset is empty. Although more of a convenience than necessary, the sqlite_fetch_single() function is slightly faster and is recommended for use with appropriate resultsets.

NOTE

sqlite_fetch_string() is an alias for sqlite_fetch_single().

Counting Resultsets and Affected Rows

Although resultsets are useful, thus far you have not yet been introduced to a way of counting the number of rows in a resultset. Although this could be accomplished using some PHP code, SQLite provides the sqlite_num_rows() function.

 sqlite_num_rows($result); 

This function takes a single parameter (the resultset resource $result) and returns the number of total rows in the resultset.

Similarly, for queries that do not return resultsets (they write to the database), SQLite provides the sqlite_changes() function, which returns the number of rows affected by the query.

 sqlite_changes($db); 

Note that unlike sqlite_num_rows(), which accepts a result handle resource, sqlite_changes() accepts a database handle resource. Thus, sqlite_changes() returns the number of rows modified during the previous SQL query.

Retrieving Field Names and Column Values

Like many database extensions, SQLite provides the capability to retrieve both the column names and each individual column value for a particular row. To begin, individual columns within a particular row can be accessed using the sqlite_column() function:

 sqlite_column($result, $key [, $decode]); 

$result is the resultset handle, $key is the column to return, and $decode determines whether the column should be decoded (from being stored using sqlite_escape_string()) prior to being returned. The $key parameter of this function can either be the name of the column itself (represented as string) or an integer representing the column (with the first column numbered zero). When executed, this function will return a string representing the value of the specified column for the current row.

To determine the actual column names returned in a resultset, SQLite provides the sqlite_field_name() function. The syntax for this function is as follows:

 sqlite_field_name($result, $field_index); 

$result is again the resultset handle and $field_index is the index (starting at one) of the column whose name you want to retrieve.

To determine the number of columns in a particular resultset (for use with the sqlite_field_name() function), SQLite provides the sqlite_num_fields() function with the following syntax:

 sqlite_num_fields($result); 

$result is the result handle resource. When executed, this function will return the total number of columns in the resultset.

Retrieving the Last Insert ID

Consider the following CREATE TABLE statement, which creates a simple table with an auto-increment key:

 CREATE TABLE autocount VALUES(id INTEGER PRIMARY KEY); 

At times it is necessary to be able to determine the last integer ID that was inserted into the database. In SQLite, this value is determined using the sqlite_last_insert_rowid() function with the following syntax:

 sqlite_last_insert_rowid($db); 

$db is the database handle resource. When executed, this function will return an integer representing the last integer ID used in an insert for an auto-incrementing column. To illustrate the use of this function, consider the following INSERT statement, which inserts a row into the SQLite autocount table defined previously:

 INSERT INTO autocount VALUES(NULL); 

Because the id column of the autocount table is inserted as NULL, SQLite automatically uses the next available unused integer as the value for that column. To select this row from within PHP, immediately following the preceding INSERT statement, the PHP code in Listing 25.4 could be used (assume $db is a valid database handle resource):

Listing 25.4. Using sqlite_last_insert_rowid()

 <?php      sqlite_query("INSERT INTO customers VALUES(NULL)");      $last_id = sqlite_last_insert_rowid();      $query = "SELECT * FROM customers WHERE id=$last_id";      $result = sqlite_query($db, $query);      $row = sqlite_fetch_array($result);      var_dump($row); ?> 

Handling Errors

Every error that occurs when using SQLite (except connecting to the database) has a unique error code assigned to it. When an error occurs in the execution of your scripts, two functions are useful to determine the error code and its meaning. The first is sqlite_last_error():

 sqlite_last_error($db); 

$db is the database handle resource in question. When executed, this function returns the integer error code of the last error that occurred for the provided database handle. Table 25.1 contains the possible constant integer values returned by a call to sqlite_last_error():

Table 25.1. SQLite Error Constants

SQLITE_OK	No error occurred.
SQLITE_ERROR	SQLite error (or database not found).
SQLITE_INTERNAL	An internal SQLite error.
SQLITE_PERM	Access permission denied.
SQLITE_ABORT	Callback routine aborted.
SQLITE_BUSY	The database file is currently locked.
SQLITE_LOCKED	A table within the database is locked.
SQLITE_NOMEM	SQLite memory allocation error.
SQLITE_READONLY	An attempt to write to a read-only database.
SQLITE_INTERRUPT	Interrupted operation.
SQLITE_IOERR	A file I/O error has occurred.
SQLITE_CORRUPT	The specified database is corrupted.
SQLITE_FULL	Database is full.
SQLITE_CANTOPEN	Could not open database file.
SQLITE_PROTOCOL	Database lock protocol error.
SQLITE_SCHEMA	The database schema changed.
SQLITE_TOOBIG	Too much data for a single row.
SQLITE_CONSTRAINT	Abort due to constraint violation.
SQLITE_MISMATCH	Data type mismatch.
SQLITE_AUTH	Authorization denied.
SQLITE_ROW	sqlite_step() has another row ready.
SQLITE_DONE	sqlite_step() has finished executing.

Because an integer constant isn't very informative for use within an error message, SQLite also provides a function that translates a given error code into a string describing the nature of the error that occurred. This transformation is done using the sqlite_error_string() function:

 sqlite_error_string($error_code); 

$error_code is the error constant returned from a previous call to sqlite_last_error(). This separation between the error value itself (which is represented by an integer) and the description of the error enables your PHP scripts to selectively deal with errors on a case-by-case basis.

Navigating Resultsets

When you're working with buffered queries (those returned from queries executed using the sqlite_query() function), it is possible to randomly access any given row within the resultset. In its most basic form, this is done using two functions, sqlite_next() and sqlite_current(), which I will explain next.

When a resultset is returned from a query, an internal pointer is used to indicate the current row being processed within PHP. The most basic operation in this regard is to move the internal row pointer ahead to the next row using the sqlite_next() function whose syntax is as follows:

 sqlite_next($result); 

$result is the result resource returned from a call to the sqlite_query() function. When executed, this function will advance the internal row pointer to the next row in the resultset. If the operation was successful, sqlite_next() returns a Boolean true. Likewise, if the operation failed (most likely because there are no more rows in the resultset), a Boolean false is returned.

Note that the sqlite_next() function does not actually return the contents of the row; rather, it does nothing more than advance the row pointer. To actually retrieve the value of the current row, the sqlite_current() function must be used. The syntax of the sqlite_current() function is as follows:

 sqlite_current($result [, $result_type [, $decode]]); 

$result is the result resource, the optional parameter $result_type is the format to return the array in (one of the constants SQLITE_ASSOC, SQLITE_NUM, or SQLITE_BOTH), and $decode is a Boolean indicating whether SQLite should automatically decode the row's data. As was the case with the other data-retrieval functions I have discussed, the $decode parameter can almost always be left as its default value of true. Likewise, if the $result_type parameter is not provided, the default SQLITE_BOTH will be used. Upon execution, the sqlite_current() function returns an array containing associative keys, numeric keys, or both for each column within the row and that row's data.

To illustrate the use of the sqlite_current() function in conjunction with the sqlite_next() function, Listing 25.5 uses each to simulate the sqlite_fetch_array() function:

Listing 25.5. Using sqlite_current() and sqlite_next()

 <?php     function my_sqlite_fetch_array($result,                                    $type = SQLITE_BOTH,                                    $decode = true) {        if(!sqlite_next($result)) {             return false;        } else {             return sqlite_current($result, $type, $decode);        }     }     $sqlite = sqlite_open(":memory:");     sqlite_query($sqlite, "CREATE TABLE test(value INTEGER PRIMARY KEY)");     for($count = 0; $count < 5; $count++) {         sqlite_unbuffered_query($sqlite, "INSERT INTO test VALUES(NULL)");     }     $result = sqlite_query($sqlite, "SELECT * FROM test");     while($row = my_sqlite_fetch_array($result)) {         var_dump($row);     } ?> 

After a resultset has been exhausted by any means (sqlite_next(), sqlite_fetch_array(), or similar) it is also possible to reset the internal row pointer to the start of the resultset using the sqlite_rewind() function. The syntax of this function is as follows:

 sqlite_rewind($result); 

$result is the result resource to rewind. For any resultset that is non empty, this function will return a Boolean true.

Although useful, using sqlite_next() offers little benefit to the already existing sqlite_fetch_array() function. It would be much more beneficial to be able to access an arbitrary row within the resultset without cycling through each row individually. To provide this functionality, SQLite has the sqlite_seek() function whose syntax is as follows:

 sqlite_seek($result, $row_number); 

$result is the resultset resource and $row_number is the zero-indexed row number to access. When executed, this function moves the internal row pointer to the appropriate row, if it exists, and returns a Boolean true. If the row does not exist in the resultset, sqlite_seek() returns false. Listing 25.6 uses the sqlite_seek() function to return a random row from the table within memory:

Listing 25.6. Using the sqlite_seek() Function

 <?php     function random_row($result) {         $t_rows = sqlite_num_rows($result);         if($t_rows > 0) {             sqlite_seek($result, rand(0, ($t_rows-1)));             return sqlite_current($result);         } else {             return false;         }     }     $sqlite = sqlite_open(":memory:");     sqlite_query($sqlite, "CREATE TABLE test(value INTEGER PRIMARY KEY)");     for($count = 0; $count < 5; $count++) {         sqlite_unbuffered_query($sqlite, "INSERT INTO test VALUES(NULL)");     }     $result = sqlite_query($sqlite, "SELECT * FROM test");     var_dump(random_row($result)); ?>