Sunday, March 05, 2006

using sqlite and php

by Justin Silverton

What is SQLite?

SQLite is a small library that implements a self-contained, embeddable, zero-configuration SQL database engine. Features include:
  • Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
  • Zero-configuration - no setup or administration needed.
  • Implements most of SQL92. (Features not supported)
  • A complete database is stored in a single disk file.
  • Database files can be freely shared between machines with different byte orders.
  • Supports databases up to 2 terabytes (241 bytes) in size.
  • Sizes of strings and BLOBs limited only by available memory.
  • Small code footprint: less than 250KiB fully configured or less than 150KiB with optional features omitted.
  • Faster than popular client/server database engines for most common operations.
  • Simple, easy to use API.
  • TCL bindings included. Bindings for many other languages available separately.
  • Well-commented source code with over 95% test coverage.
  • Self-contained: no external dependencies.
  • Sources are in the public domain. Use for any purpose.
Why would I need this?

If you want to have the advantages of a SQL database, without having to install a separate system such as mysql or postgres. It has also been demonstrated to work on websites getting more than 1,000,000 hits per day.

lots of good performance info can be found here
project page can be found here


Read the INSTALL file, which comes with the package. Or just use the PEAR installer with pear install sqlite. SQLite itself is already included, You do not need to install any additional software.

Windows users may download the DLL version of the SQLite extension here: (php_sqlite.dll).

In PHP 5, the SQLite extension and the engine itself are bundled and compiled by default. However, since PHP 5.1.0 you need to manually activate the extension in php.ini (because it is now bundled as shared). Moreover, since PHP 5.1.0 SQLite depends on PDO it must be enabled too, by adding the following lines to php.ini (in order):

On Linux or Unix operating systems, if you build PDO as a shared extension, you must build SQLite as a shared extension using the --with-sqlite=shared configure option.

SQLite 3 is supported through PDO SQLite.

Windows installation for unprivileged accounts: On Windows operating systems, unprivileged accounts don't have the TMP environment variable set by default. This will make sqlite create temporary files in the windows directory, which is not desirable. So, you should set the TMP environment variable for the web server or the user account the web server is running under. If Apache is your web server, you can accomplish this via a SetEnv directive in your httpd.conf file. For example:

SetEnv TMP c:/temp
If you are unable to establish this setting at the server level, you can implement the setting in your script:

The setting must refer to a directory that the web server has permission to create files in and subsequently write to and delete the files it created. Otherwise, you may receive the following error message: malformed database schema - unable to open a temporary database file for storing temporary tables

Using SQlite

The following is a simple example that can get you started with SQLite:

// create new database
$db = sqlite_open("db.sqlite");

//create a table named test
// sqlite_query($db , "CREATE TABLE test (id INTEGER PRIMARY KEY, name CHAR(128))");

// insert sample data
sqlite_query($db, "INSERT INTO test (name) VALUES ('Imuvalue')");

// execute query
$result = sqlite_query($db, "SELECT * FROM test");
// iterate through the retrieved rows
while ($row = sqlite_fetch_array($result))
// close database connection


Post a Comment

Links to this post:

Create a Link

<< Home