There is a great misconception that SQLite is not available for PHP4,
rather only for PHP5. In fact, SQLite support has been around for quite
some time courtesy of the bindings available via the PHP Extension
Community Library (PECL). This tutorial is intended to show you how to
install the SQLite PECL Extension in PHP4. Following its installation I
will walk through a basic example of using SQLite to access a database
stored in a flat file.
So what is SQLite and what makes it so special? At their most basic
level the vast majority of Database Engines (MySQL, Access, SQL Server,
Postgres, etc) store their data as flat files on the file system. The
application engine exists to allow for enhanced functionality such as
user authentication, key management, etc. SQLite, however, does not
represent your typical database, primarily because it lacks such an
engine. Because of this it is unable to incorporate some of the advanced
features found in high end database engines. Rather it is designed to be
simple, easy to use, and capable of operating with decreased overhead
making it especially useful for embedded systems.
For example, in PHP when you send a query to MySQL database it follows a
client-server architecture where PHP talks to MySQL which then talks to
its flat files. SQLite on the other hand is, itself, the server and
therefore allows us to drop the middleman and have PHP talk directory to
the files where the data is stored. Step 1: Acquire and install the
software extension tarball from PECL.
This can be done either by visiting the PECL website
(http://pecl.php.net), or using the PEAR Package Manager
(pear download SQLite). Perhaps the simplest shortcut is to, from the command line, run
'pear install SQLite'. In this matter both the acquisition and
installation of the extension are completed with one simple command.
Note: When I did this, I had to do explicitly create the copy path,
listed at the end of the install process. If you encounter this problem
be sure to see what directory the installer is attempting to copy to,
create it, and run the installer again.
For those of you who wish to build the module yourself, this can be done
in a few steps. The first step, of course, is to obtain the tarball and
untar it. Once this is complete, enter the newly created directory.
Next run the 'phpize' program which will in effect build a module tree
from the included files that can be configured and installed. Note: If
you get a 'command not found' when you attempt to run phpize, make sure
it is in your path and that your copy of PHP came with the binary
(/path/to/php/bin). If you obtained PHP from a vendor package, you will
need the development package as well. Once this is complete you are
ready to configure the module for installation. This is done simply by
using ./configure --with-sqlite.
Following a successful ./configure, su to root and make all install.
This will compile the extension and place the .so file in a subdirectory
under the extensions directory. As previously stated, you may have to
create this directory path, if it doesn't already exist. In the end, you
should issue the following commands to the Command Line Interface.
make all install
(Commands courtesy PECL-Dev Mailing List Member Sara Golemon)
Step 2: Configure Dynamic Load of the Module in php.ini
Now that we have the module correctly installed, we need to make sure
PHP loads it when it is executed by Apache. This is done the same way
extensions are enabled on a Win32 system, by using dynamic extension
loading. Open your php.ini file and find the directive 'extension_dir'
which defines where PHP will look for external modules. Make sure that
the sqlite.so file is under this directory. In my case, I generally
create a symlink from the original file to this location.
Next scroll down and add the module name the list of modules in the
As explained in the INI file, no other information is needed here save
the EXACT name of the .so file created during the install. When finished
you should have these two similar lines in the INI file.
Step 3: Reloading Webserver and Checking New Settings
At this point, restart your webserver. Using the phpinfo() function you
should now see a page listing a section indicating SQLite support
installed. Next we will proceed to describing the basics of interacting
with an SQLite database. Keep in mind, most of the following can be
found at http://www.php.net/sqlite.
This code very simply opens up (or creates and then opens) a file
'database'. If you have worked with ANY database extension within PHP
the next command should be obvious to you. We are going to select
everything from the table foo but only keep one row. Then we fetch the
results in an array representing the single row. Then we use print_r to
show what values were fetched from the row.
As discussed earlier, there being no engine in SQLite, the sqlite_open
function serves to both establish a connection and select the database.
I would like to thank the following people for their time in helping me
write this article:
Joey Smith (TML)
Sara Golemon (Pollita)