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.

        cd foo
        phpize
        ./configure --with-sqlite
        su
        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 form:

        extension=module.extension

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.

        extension_dir = /path/to/php/extensions/
        extension = sqlite.so

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.


 

        <?php

            $con = sqlite_open("database");

            $set = sqlite_query("select * from foo limit 1");

            $row = sql_fetch_array($set);

            sqlite_close($con);

            print_r($row);

        ?>

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:

Generic_Badass
Joey Smith (TML)
Sara Golemon (Pollita)