SQLite: A Lightweight SQL Engine

PHP version 5 comes bundled with a SQL library that works with flat files, rather than with a database server. This is useful for writing PHP scripts in environments that don't provide access to MySQL or to another third-party SQL server.

In this section we will discuss PHP's SQLite functions. You shouldn't need to do anything special to install SQLite, so let's get straight to it with some code to open or create a new database. Because SQLite works with your file system, you need to work with a directory your script can write to:

$db = "data/testdb";
$dbres = sqlite_open($db, 0666, $error);
if ( ! is_resource( $dbres ) ) {
  die( "sqllite error: $error" );
}

The sqlite_open() function requires a path to a database file, a mode, and an error variable. The mode argument is not currently used by the SQLite functions, but you should use 0666 as a placeholder so the $error argument can be passed to the sqlite_open() function.

The function returns a resource, which we use to work with SQLite; otherwise, it returns false if an error is encountered. We store the return value in $resource and test it. If $resource contains false, we print the contents of the $error variable, which will have been populated with error information.

Creating a Table in a SQLite Database

Now that we have opened or created a database, we can create a table with which to work. We execute SQL statements with the sqlite_query() function, which requires a SQLite database resource and a string containing the query to execute. For queries that return no resultset, the function returns true if the process was successful and false if an error occurred. Let's drop and create a table:

@sqlite_query( $dbres, "DROP TABLE people" );
$create = "CREATE TABLE people ( id INTEGER PRIMARY KEY,
                 firstname varchar(255),
                 secondname varchar(255) )";
sqlite_query( $dbres, $create );

We use a DROP statement to ensure that no people table is in place when we create one. Notice that we use an @ character in front of our first call to sqlite_query(). This suppresses the error message we will encounter the first time this script is run:

Warning: sqlite_query(): no such table: people

We then write the table. Although SQLite does not complain about the CREATE statement we use, the VARCHAR types we specify are actually irrelevant. SQLite treats all its fields as strings, regardless of the field declaration. The only exception to this is our first field. We have declared the id field as INTEGER PRIMARY KEY, ensuring that the id field will contain an integer value that will be incremented automatically as rows are entered.

Entering and Updating Data

Now that we have encountered the sqlite_query() function, we are ready to add some data:

$insert = array(
      array( "firstname" => "joan",
          "secondname" =>"peel" ),
      array( "firstname" => "mary",
          "secondname" =>"biscuit" )
);

foreach ( $insert as $row ) {
  $insert = "INSERT INTO people ( 'firstname', 'secondname' )
             VALUES( '{$row['firstname']}',
                 '{$row['secondname']}' )";
  sqlite_query( $dbres, $insert );
  print "Inserting {$row['firstname']} {$row['secondname']}: ";
  print "id: ".sqlite_last_insert_rowid( $dbres )."<br />\n";
}

In fact, most of the previous example is taken up with the creation of an array and with reporting back to the user. We create a multidimensional array containing two array elements, each containing firstname and secondname elements. We loop through this, building a SQL string for each iteration and passing it to the sqlite_query() function. We use a new function, sqlite_last_insert_rowid(), to get the auto-incremented id for our insert. It is often useful to know the value of an auto-incremented id after we have inserted a row. The sqlite_last_insert_rowid() function saves us the trouble of a second query. The output from the previous fragment confirms that we have acquired id values:

Inserting joan peel: id: 1<br />
Inserting mary biscuit: id: 2<br />

Of course, we can also run an update query, as shown here:

$update = "UPDATE people SET firstname='John' where secondname='peel'";
sqlite_query( $dbres, $update );