Using the PHP SQLite Extension

Now we know that our PHP supports the SQLite interface, let's look at the set of commands that are available for communicating with a SQLite database.

Opening a Database

The PHP function to open a SQLite database is sqlite_open() and its prototype is given next:

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

The filename parameter specifies the database name, and if the file specified does not exist it will be created. A relative or absolute path to the database file can be provided; otherwise, PHP looks for filename in the same directory as the script being executed. To open an in-memory database, use :memory:.

Although PHP can execute scripts from the command line, this chapter assumes that you are mostly using PHP in a web environment, where file permissions can be problematic. In order for PHP to open or to select from a SQLite database, the user under which the web server is running must have read permissions on the database file.

To perform an UPDATE, INSERT, or DELETE, that web server userid must also not only have write permissions on the database file itself but also must be able to create files within the same directory so that the journal file can be written.

Apache often runs as the user nobody or apache and so care needs to be takenparticularly on a shared systemif the database file is made writable to this user or, worse, if the file is made world-writable.

To prevent other users of the same web server from accessing your databases, PHP should be run in safe mode. An alternative is to use suPHPavailable from http://www.suphp.org/so that PHP can run as a number of different users on the same server.

The mode parameter to sqlite_open() specifies the mode of the file, and is octal 0666 by default. The intended use of this parameter is to open a database file in read-only mode, using mode 0444 for example.

The resource returned is a database handle if sqlite_open() is successful. On failure, the function returns FALSE and if the optional errmessage parameter is passed by reference it will contain a descriptive error message explaining why the database cannot be opened.

In Listing 5.2 we use sqlite_open() to open a new database webdb. To follow the rest of the examples in this chapter, make sure this database is created with the correct file permissions to be opened by PHP without any error.

Listing 5.2. Script to Open a Database with Error Checking

 <?php if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   echo "SQLite database opened"; } ?> 

PHP allows for a persistent database connection to be used via the sqlite_popen() function, which takes the same parameter list as sqlite_open().

Persistent connections reduce the overhead of repeatedly connecting to the same database. Where a persistent handle has already been opened to the specified database, that handle is made available to the script. If no persistent connection is available, a new connection is created.

Note

Persistent connections in PHP take advantage of the nature of multiprocess or multithreaded web servers. As long as PHP is built into Apache as a module or loaded into the web server using a supported SAPI, persistent connections will be available. If you are running PHP as a CGI wrapper, there is no persistence between page requests and although the sqlite_popen() function is still available, it will behave no differently than sqlite_open(). For more information, see http://php.net/manual/en/features.persistent-connections.php.

To close a SQLite database session, use sqlite_close(). The only parameter is the database resource returned by the function that opened the connection. If sqlite_popen() was used, the persistent connection will be closed and will no longer be available to other processes.

Passing Queries and Commands to SQLite

To pass an SQL command to SQLite, use the sqlite_query() function. Two parameters are requireda database connection resource and the query text itself. The prototype is given here:

 resource sqlite_query (resource dbhandle, string query) 

In fact the parameters can be specified in reverse order to that shown in the preceding example for compatibility with other PHP database extensions. However, the preferred syntax is to give the dbhandle parameter first for consistency with other SQLite APIs.

Note

The terminating semicolon is not required when a single query is executed through sqlite_query(). However, the PHP extension does allow you to submit multiple commands at the same time by passing a single string of SQL statements with each terminated by a semicolon.

For a SELECT statement, resource will be a result handle that can be processed by further functions, as we will see shortly. The result will be FALSE if a SELECT query fails.

Where query does not return any rows, for instance an UPDATE, INSERT, or DELETE statement or a CREATE TABLE as shown in the example in Listing 5.3, the resource value returned is trUE if the statement is executed successfully; otherwise, it is FALSE.

To find the reason a query has failed, use the sqlite_last_error() and sqlite_error_string() functions to retrieve the error message from SQLite. The former returns a numerical error code, which the latter decodes into a human-readable error message, as demonstrated in Listing 5.3.

Listing 5.3. Executing a CREATE TABLE Statement Through the PHP Extension

 <?php if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   $sql = "CREATE TABLE contacts (            id INTEGER PRIMARY KEY,            first_name CHAR,            last_name  CHAR,            email      CHAR          )";   $res = sqlite_query($db, $sql);   if (!$res) {     echo "Error with query:";     echo "<PRE>$sql</PRE>";     echo sqlite_error_string(sqlite_last_error($db));   }   else {     echo "Command successful";   } } ?> 

The first time Listing 5.3 is executed, the table contacts will be created. However, a second execution will produce an error message similar to that shown in Figure 5.2.

Figure 5.2. Error messages displayed when a SQLite query fails.

The screenshot was taken on a system that has display_errors turned on in php.ini, and you can see that the feedback from the PHP extension is generally more useful than the SQLite error message returned.

Using Commands That Change the Database

The apostrophe or single quote character needs to be delimited before it can be stored in a SQLite column. Two apostrophes appearing together are treated as a single character within a string enclosed by single quotes.

For instance this statement is invalid:

 INSERT INTO mytable (myfield) VALUES ('It's tricky'); 

It should be rewritten as follows, which will insert the string It's tricky containing a single apostrophe into the database:

 INSERT INTO mytable (myfield) VALUES ('It''s tricky'); 

The function sqlite_escape_string() can be used to automatically make strings compatible with SQLite, by escaping single quote characters and encoding binary-unsafe characters. Listing 5.4 contains a PHP script that loops through an array of contact details and safely inserts each record into the database.

Note

The PHP function addslashes() is not suitable for escaping quotes in values intended for storage in SQLite.

Listing 5.4. Escaping Special Characters Before Inserting Data

 <?php $contacts = array(array("first_name" => "Ronnie",                         "last_name"  => "O'Sullivan",                         "email"      => "[e-mail]"),                   array("first_name" => "Shaquille",                         "last_name"  => "O'Neil",                         "email"      => "[e-mail]"),                   array("first_name" => "John",                         "last_name"  => "O'Shea",                         "email"      => "[e-mail]")); if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   foreach ($contacts as $c) {     // Escape each value before building INSERT statement     foreach ($c as $field => $value) {       $c[$field] = sqlite_escape_string($value);     }     $sql = "INSERT INTO contacts (first_name, last_name, email)             VALUES ('$c[first_name]', '$c[last_name]', '$c[email]')";     $res = sqlite_query($db, $sql);     if (!$res) {       echo "Error with query.";       echo "<PRE>$sql</PRE>";       echo sqlite_error_string(sqlite_last_error($db));     }     echo "Inserted $c[first_name] $c[last_name]<br />\n";   } } ?> 

In lines 3 to 11 we declare an array of the contact information that will be inserted. The outer loop, opened on line 20, extracts each of the inner arrays into $c, and then each value within $c is escaped before the query string is built.

As the contacts table includes an INTEGER PRIMARY KEY, a unique number is assigned to the id column each time an INSERT is performed. The value assigned can be retrieved using the sqlite_last_insert_rowid() function.

Replace line 36 in Listing 5.4 with the following code to display the value of the id column for each record inserted:

 $id = sqlite_last_insert_rowid($db); echo "Inserted $c[first_name] $c[last_name] as ID $id<br />\n"; 

Whenever an UPDATE, INSERT, or DELETE statement is issued, it's likely that one or more rows in a table have been modified in some way. The function sqlite_changes() will return the number of affected rows and can be used to confirm that the command has done what it was intended to do.

Listing 5.5 issues an UPDATE statement converting each first_name and last_name value to uppercase in the contacts table and shows the number of rows affected by this command.

Listing 5.5. Finding the Number of Rows Affected by an UPDATE Command

 <?php if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   $sql = "UPDATE contacts           SET first_name = upper(first_name),               last_name  = upper(last_name)";   $res = sqlite_query($db, $sql);   if (!$res) {     echo "Error with query.";     echo "<PRE>$sql</PRE>";     echo sqlite_error_string(sqlite_last_error($db));   }   $changes = sqlite_changes($db);   echo "Updated $changes row(s)<br />\n"; } ?> 

Note

The number reported by sqlite_changes() is the number of matching rows in the WHERE clause of an UPDATE statement, or the total number of rows in the table if there is no WHERE. It is not necessarily the number of rows changed by the command; where a row is updated to the same value it previously held, this is still counted in the total number of changes.

Working with Retrieved Data

The resource returned by sqlite_query() for a SELECT statement can be used in conjunction with a number of PHP functions to fetch the data returned by the query and to find information about the dataset itself.

The functions sqlite_num_rows() and sqlite_num_fields() both take a single resource parameter and return an integer value of the number of rows and columns in the returned dataset respectively.

To fetch a column name, use sqlite_field_name(). The function takes two parameters, the resource returned by sqlite_query() and a column identifier specified as an index number. Where the columns are selected in the FROM list of the query, the leftmost column in the list has index 0. If SELECT * FROM tablename is used, the columns are numbered from zero in the order that they appear in the database schema.

The function to fetch a column value is sqlite_column(), which takes the same two parameters as sqlite_field_name(), but this time the column identifier may be either an index number or the name of the column, if it is known.

For instance, if the query passed was

 SELECT first_name, last_name FROM contacts; 

the following two statements are equivalent:

 $value = sqlite_column($res, 1); $value = sqlite_column($res, "last_name"); 

Immediately after sqlite_query() has been executed, the values returned by sqlite_column() are from the very first row of the dataset. To shift the result handle pointer to the next row, use sqlite_next().

The most common usage is to seek to each row in turn using sqlite_next() in a loop structure. To find out when the end of the dataset is approaching, use sqlite_has_more() with a single resource parameter, which returns trUE as long as there are still more rows to fetch. If you call sqlite_next() when there are no more rows to be fetched, a warning will be shown.

The example in Listing 5.6 puts together the functions we have briefly introduced in this section to execute a query and display the contents of the returned dataset in a formatted HTML table. The table column headings are the database column names, and each record is displayed in a table row in turn until there are no records left.

Listing 5.6. Displaying the Results of a Query in an HTML Table Using PHP

 <?php if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   $sql = "SELECT * FROM contacts           ORDER BY last_name, first_name";   $res = sqlite_query($db, $sql);   if (!$res) {     echo "Error with query.";     echo "<PRE>$sql</PRE>";     echo sqlite_error_string(sqlite_last_error($db));   }   echo sqlite_num_rows($res)." row(s) found<br />\n";   echo "<TABLE BORDER=1>\n";   echo "<TR>\n";   for ($i=0; $i<sqlite_num_fields($res); $i++) {     echo "<TH>" . sqlite_field_name($res, $i) . "</TH>\n";   }   echo "</TR>\n";   while (sqlite_has_more($res)) {     echo "<TR>\n";     for ($i=0; $i<sqlite_num_fields($res); $i++) {       echo "<TD>" . sqlite_column($res, $i) . "</TD>\n";     }     echo "</TR>\n";     sqlite_next($res);   }   echo "</TABLE>"; } ?> 

The output should look similar to that shown in Figure 5.3.

Figure 5.3. Output from Listing 5.6, showing the result of a query displayed in an HTML table.

Let's look at parts of this code in more detail. After we have passed the query to SQLite using sqlite_query(), we display a count of the number of rows that will be displayed in line 20.

 echo sqlite_num_rows($res)." row(s) found<br />\n"; 

Next, after opening an HTML <TABLE> tag, we generate a row of table headings<TH>by counting through the number of fields in the dataset and outputting the name of each one in turn.

 echo "<TR>\n"; for ($i=0; $i<sqlite_num_fields($res); $i++) {   echo "<TH>" . sqlite_field_name($res, $i) . "</TH>\n"; } echo "</TR>\n"; 

We then use the same for loop as in the preceding lines within another while loop to fetch a row at a time and display each field value in turn inside <TD> tags, to align with the column headings generated previously.

 while (sqlite_has_more($res)) {   echo "<TR>\n";   for ($i=0; $i<sqlite_num_fields($res); $i++) {     echo "<TD>" . sqlite_column($res, $i) . "</TD>\n";   }   echo "</TR>\n";   sqlite_next($res); } 

In fact the outer while loop could also be performed as a for loop by counting up to the value returned by sqlite_num_rows(). The method you use will be a matter of preference.

Another way to fetch rows of data is using sqlite_current() or sqlite_fetch_array(). Both functions return an associative array of all the values from the next row using the column names as keys, with the latter also advancing the result handle pointer to the next row, as if sqlite_next() had been called.

After executing this command

 $row = sqlite_current($res); 

the following two statements are equivalent:

 $value = sqlite_column($res, "first_name"); $value = $row["first_name"]; 

The default behavior of sqlite_fetch_array() and sqlite_current() is to return an associative array with columns indexed both by their field name and their numerical position beginning at zero. Therefore if first_name was the first column listed, the following statement is also equivalent:

 $value = $row[1]; 

An optional second parameter can be passed to either function to define this behavior. The valid constants are SQLITE_ASSOC, SQLITE_NUM, and SQLITE_BOTH, which instruct PHP to create an associative array, a numerically indexed array or an array with both sets of indexes respectively.

For instance, if the command executed is

 $row = sqlite_current($res, SQLITE_NUM); 

$row["first_name"] will not contain any value.

Note

The configuration directive sqlite.assoc_case in php.ini determines the case of the keys used in the associative arrays returned by sqlite_current() and sqlite_fetch_array(). The default value is zero for mixed-case keys that match the column names exactly. When this setting is 1 or 2, the key names are converted to all uppercase or all lowercase respectively.

The following section of code could replace lines 30 to 37 of Listing 5.6, performing the same loop using do ... while:

 while ($row = sqlite_fetch_array($res)) {   echo "<TR>\n";   for ($i=0; $i<sqlite_num_fields($res); $i++) {     echo "<TD>" . $row[$i] . "</TD>\n";   }   echo "</TR>\n"; } 

If you are interested only in the first row returned by a query, the function sqlite_fetch_single() returns an associative array of values from the very first row in the dataset. Because the result handle pointer is not moved and its position does not affect the behavior of this function, sqlite_fetch_single() is the most optimal way to return data to PHP when only the first row is required.

The PHP extension also provides a single function for returning the entire result of a query into a single array. The following code line gives the prototype for sqlite_array_query(), and as with sqlite_query(), the order of the query and dbhandle parameters can be reversed for compatibility with other PHP database extensions.

 array sqlite_array_query ( resource dbhandle, string query [, int result_type]) 

This function returns a single, two-dimensional array made up of one array of associative arrays of values for each record in the dataset.

The same functionality could be implemented quite easily in your PHP script using a sqlite_query() and a loop on sqlite_fetch_array(), but as a built-in function of the PHP SQLite extension, it is significantly faster to use this function that builds the array in PHP. The following two pieces of code have the same result:

 $data = sqlite_query($db, $sql); $res = sqlite_query($db, $sql); while($row = sqlite_fetch_array($res)) {   $data[] = $row; } 

The contents of the $data array that was generatedby either methodfor the query SELECT * FROM contacts is shown in Listing 5.7, using the print_r() function to recursively output the array elements.

Listing 5.7. Array Returned by sqlite_array_query() Function

 Array (     [0] => Array         (             [0] => 2             [id] => 2             [1] => Shaquille             [first_name] => Shaquille             [2] => O'Neil             [last_name] => O'Neil             [3] => [e-mail]             [email] => [e-mail]         )     [1] => Array         (             [0] => 3             [id] => 3             [1] => John             [first_name] => John             [2] => O'Shea             [last_name] => O'Shea             [3] => [e-mail]             [email] => [e-mail]         )     [2] => Array         (             [0] => 1             [id] => 1             [1] => Ronnie             [first_name] => Ronnie             [2] => O'Sullivan             [last_name] => O'Sullivan             [3] => [e-mail]             [email] => [e-mail]         ) ) 

The PHP extension allows random access of the rows in a dataset using the sqlite_seek() function, for which the prototype is given here:

 bool sqlite_seek (resource result, int rownum) 

The function attempts to set the result handle pointer to row rownum for the given resource, and returns trUE on success or FALSE if the rownum specified does not exist.

Listing 5.8 shows how sqlite_seek() can be used to jump to a random row within the dataset returned from our sample contacts database. Execute this script a few times to see that a random selection is made each time.

Listing 5.8. Using sqlite_seek() to Jump to a Specific Row

 <?php if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   $sql = "SELECT * FROM contacts           ORDER BY last_name, first_name";   $res = sqlite_query($db, $sql);   if (!$res) {     echo "Error with query.";     echo "<PRE>$sql</PRE>";     echo sqlite_error_string(sqlite_last_error($db));   }   sqlite_seek($res, rand(0, sqlite_num_rows($res)-1));   $row = sqlite_fetch_array($res);   print "<PRE>";   print_r($row);   print "</PRE>"; } ?> 

The function sqlite_rewind() takes a single resource parameter and resets the result handle pointer to the first record in the dataset. The following two commands are equivalent:

 sqlite_rewind($res); sqlite_seek($res, 0); 

For these operations to work, the result handle must be buffered and seekable, and creating this data structure in memory is an overhead that can be avoided. The function sqlite_unbuffered_query() works just the same as sqlite_query() except that the result handle can only be accessed sequentially. As only one row is returned at a time, this gives much better performance and is recommended unless random access is a necessity.