Many people have heard of SQLite but not many people have used it in their PHP applications. Most PHP applications make use of the MySQL database engine to store information however this is not always needed for small amounts of data. In these cases, web applications often store their data in a "flat file" format, often just a plain text file or an XML file. Both of these methods have their downsides. For examples, if you distribute the web application, your customers may not have access to a MySQL server. In the case of flat files, these can quickly grow large and inefficient and can be difficult to query.

SQLite attempts to resolve this problem by offering a SQL database without the need for a SQL database server. It does this by implementing the SQL server in your application code (or in the case of PHP, within the PHP engine). The databases themselves are stored on the file system as regular binary files in SQLites structure. You then access this file using PHP as if it where stored on a regular SQL database server using the SQLite functions such as sqlite_query() and sqlite_fetch_all(). This has the benefit of being able to do complex queries across multiple tables without having the overhead of a dedicated SQL database server.

Note: SQLite support was introduced in PHP 5 so you will need at least PHP v5.0.0 to use it and you may need to enable it in your php.ini.


Connecting to and Creating Databases

As SQLite databases are stored as a regular file you have two options for distributing them with your web applications.

The first is to create the database yourself (eg, myapp.db) then ship it with your PHP files for the user to unzip and upload to their web space.

The second is to use the MySQL route and create the database in your installer (or application) PHP code.

To open (and create) your database, you will use the sqlite_open() function. This will open an existing database or create it if it doesn't exist.

Example:
As you can see, sqlite_open() takes three parameters. The first paramater is required, the other two are optional.

The first is the name of the database, in this case 'talkphp.db'. It's important to note that your database does not need to have the .db extension (or any extension) but it is helpful to give it an extension to distinguish it from your other files.

The second is the mode to open the database with. These are standard Unix file permissions and this is intended to allow you to open the database in read-only mode. The recommended setting for this is 0666.

The third is the name of a variable to store any error message in. If sqlite_open() was unable to open or create your database the error text would be stored in this variable.

Security Tip: I would suggest that your store your databases outside of the webroot. This will stop people downloading your entire database if they know the name of it. Eg: http://www.example.com/myApp/talkphp.db would download the file. An alternative approach is to put the database in a separate directory within the webroot and put a .htaccess file in the directory with a "Deny from all" rule to prevent visitors downloading your database.


Creating Tables

Once you have your database created, the next step is to add some tables to it. This is achieved using standard SQL commands and the sqlite_query() function.

Example:
This will create a table named "articles" in the database which we previously connected to ($db) with two columns - "title" and "description".


Inserting Data

The next step is to put some data into our new table. Again we will use the sqlite_query() function to do this using standard SQL commands.

Example:
This command inserts a row into the "articles" table in the "talkphp" database (which we previously connected to and assigned to the $db variable).

If you have used MySQL or any other SQL database in PHP before then all this should look very familiar.


Selecting Data

It should be no surprise by now to find that we are going to use the sqlite_query() function again to select data from our new database.

Example:
This will return all values in the "title" and "description" columns in the "talkphp" table using the database connection "$db". These values are stored in $result which is a SQLiteResult object.

You can then access these results as you would in MySQL but using the SQLite functions. We won't go into details here since they are all very similar to the MySQL functions but the functions of note are sqlite_fetch_all() and sqlite_fetch_array().


Additional useful functions

As expected, SQLite also contains functions to escape strings (sqlite_escape_string()), get the number of rows in the result set (sqlite_num_rows()) and many more. For a full list, check the SQLite section of the PHP manual.


Conclusion

As you have seen, accessing SQLite databases is very similar to accessing MySQL databases with the benefits of being able to access data in a uniform (SQL) way without having the overhead of a full SQL server.

discuss this topic to forum