PHP5 comes bundled with a great new feature: a built-in SQLite extension that allows you to store data in a database without any database server at all. The functions work in much the same way as the corresponding MySQL functions in PHP, except there is no need to have a separate database server running alongside PHP.
I will not cover how to setup SQLite in detail. For me, it was as simple as uncommenting the following three lines in my php.ini file:
extension=php_pdo.dll
extension=php_pdo_sqlite.dll
extension=php_sqlite.dll
If you need more help setting up SQLite, take a look at the PHP documentation.
Note: There are two different PHP extensions for SQLite. The one we will be focusing on uses procedural functions. The other extension is designed for SQLite3 and uses the class SQLite3. You can read more information about the SQLite3 extension in the PHP manual.
Creating a Database Connection
After you have installed and setup SQLite, you can begin using it right away. Those who have used PHP’s MySQL extension will find themselves right at home. Opening a connection to an SQLite database is quite simple:
<?php
if ($db = sqlite_open('my_sqlite_db', 0666, $sqlite_error))
{
}
else
{
die ($sqlite_error);
}
?>Let us examine the code above bit by bit. The if statement is used to catch errors whilst opening a connection to SQLite, similar to MySQL’s mysql_error(). The important bit of code above is the sqlite_open() function.
The first parameter of sqlite_open() is the name of the database you want to open. You can think of this as the database name in MySQL, except that if the database does not exist, it will be created for you. The database will be created in the same directory as the PHP file that calls the function. If this file is in a path that is accessible to anyone, they will be able to access your database and to view the contents of it. Obviously, this is a major security risk. To remedy this, you need to enter a relative path out of the web root, such as:
../../../database_name
So, your code might change to something like this:
<?php
if ($db = sqlite_open('../../../my_sqlite_db', 0666, $sqlite_error))
{
}
else {
die ($sqlite_error);
}
?>The second parameter is the mode. This is intended to be used to open databases in read-only mode. Most of the time you will want to use the value 0666, which allows full access to the database. Again, to draw a similarity to MySQL, this is like opening a database with a user that has full permissions (which is how most of us use MySQL).
The last parameter is an error-handling variable that stores any error messages that occur. If an error occurs, you can echo this variable to see what went wrong, as we did in the previous examples.
The sqlite_open() function returns a database handler that is used in the other SQLite functions. This database handler is used in SQLite functions in the same way as the optional second parameter of mysql_query() is used. By default, mysql_query() uses the last link opened by mysql_connect(), so passing a database handler to the function is optional. We save the return value in the $db variable for later use because the SQLite extension does not assume the last opened connection like the MySQL extension does.
Running Queries
After you have connected to the database, you will want to start running queries. To do this, you need to use another function called sqlite_query():
<?php
if ($db = sqlite_open('../../../my_sqlite_db', 0666, $sqlite_error))
{
sqlite_query($db, 'CREATE TABLE test ( name varchar(10) )');
sqlite_query($db, "INSERT INTO test VALUES ('lowter')");
}
else
{
die ($sqlite_error);
}
?>The code above will create a new table called test and insert a new row with a name value of lowter. The first parameter is always the database handler, which was stored in the $db variable when we ran sqlite_open(). The second parameter is the actual SQL query you want to run. You can look at the SQLite documentation for a full list of SQL syntax.
Note: Some of the SQL syntax in SQLite is different than in MySQL, so if you run into any trouble you may want to take a look at the documentation linked to above.
After you have inserted some data into the database, you will want to retrieve it. To do this, we will run a SELECT query and use a few more functions to extract the data:
<?php
if ($db = sqlite_open('../../../my_sqlite_db', 0666, $sqlite_error))
{
$result = sqlite_query($db, 'SELECT name FROM test');
while ($row = sqlite_fetch_array($result))
{
echo 'Name: '.$row['name'].'<br />';
}
}
else
{
die ($sqlite_error);
}
?>If you are familiar with MySQL, then this code should look familiar to you. The $result variable holds all of the results from the SQL query. The while loop goes through each retrieved row and extracts the data as an array using sqlite_fetch_array(). We then use this array to output the data.
Summary
There are a lot of other functions that are available in PHP’s SQLite extension. If you want to see a full list of all of them, take a look at the SQLite extension in the PHP manual.
Hopefully this article has given you a quick overview into the power that SQLite brings to PHP. I have been using it a lot recently as a caching mechanism because it does not require a separate database server or having to deal with a lot of cache files. For more information about SQLite in general, make sure to read Lowter’s article SQLite Overview.
