One of the innovations in PHP
5.x is the inclusion of the SQLite database engine. SQLite is a fully self-contained, portable file-based
database engine that can be used for most SQL operations without incurring the
overhead of client-server communication. This SQLite
API is activated by default in PHP 5.x, which means that you can get up and
running with SQLite instantly.

This tutorial will show you how to use PHP to interact with anSQLite database, by introducing
you to the important methods supported by the SQLite
API, and providing a simple script template for you to use in your development.
It assumes that you have a working Apache and PHP installation.

Editor’s Note:
The two code listings mentioned in this document are available in a more easily
manageable text form by downloading this file.

It is not strictly necessary to have the interactive SQLite program installed on your system; however, for
simplicity in creating the initial set of tables for this tutorial, you should
download and install this program. Then, create an example table for your SQL queries, by
creating an empty text file, executing the binary with the file name as the
parameter and entering the following commands (Listing A) at the interactive command prompt:

Listing A

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, country TEXT);
sqlite> INSERT INTO users VALUES (1, ‘john’, ‘IN’);
sqlite> INSERT INTO users VALUES (2, ‘joe’, ‘UK’);
sqlite> INSERT INTO users VALUES (3, ‘diana’, ‘US’);

Once the table has been created, proceed to build a script
template using PHP SQLite methods. (Listing B)

Listing B

<?php
// set access parameters
$db = “users.db”;

// open database file
// make sure script has read/write permissions!
$conn = sqlite_open($db) or die (“ERROR: Cannot open database”);

// create and execute INSERT query
$sql = “INSERT INTO users (id, username, country) VALUES (‘5’, ‘pierre’, ‘FR’)”;
sqlite_query($conn, $sql) or die(“Error in query execution: ” . sqlite_error_string(sqlite_last_error($conn)));

// create and execute SELECT query
$sql = “SELECT username, country FROM users”;
$result = sqlite_query($conn, $sql) or die(“Error in query execution: ” . sqlite_error_string(sqlite_last_error($conn)));

// check for returned rows
// print if available
if (sqlite_num_rows($result) > 0) {
while($row = sqlite_fetch_array($result)) {
echo $row[0] . ” (” . $row[1] . “)\n”;
}
}

// close database file
sqlite_close($conn);
?>

There are four simple steps to follow when executing an SQL
query with the PHP SQLite extension:

  1. Begin by initializing a database
    handle with a call to the sqlite_open()
    function. The path and file name of the database (remember, SQLite is file-based, not server-based like MySQL) is
    passed to the function as an argument.
  2. Create the SQL query string, and
    execute it with the sqlite_query() function. The result object of
    these methods will be different depending on the query type and whether or
    not it was successful. Successful SELECT queries
    return a result object; successful INSERT/UPDATE/DELETE queries
    return a resource identifier; and unsuccessful queries return false. The sqlite_error_string() and sqlite_last_error() methods can be used to catch
    errors and display appropriate error messages.
  3. For SELECT queries, the result object
    may be processed further to extract data from it. Used in a loop, the sqlite_fetch_array() function retrieves each record as
    a PHP array. You access individual fields of the record by calling the
    appropriate key of the array.
  4. End the session with a call to the
    sqlite_close() function.

Hopefully, this script template will save you some time when
you next sit down to write SQLite
connection/interaction routines in PHP. Happy coding!