PHP and SQLite

Using PHP with MySQL</td> </table>

Purchase and download the full PDF and ePub versions of this PHP eBook for only $8.99

SQLite is an embedded database that is bundled with PHP starting with PHP 5 and implements a large subset of the SQL 92 standard.

SQLite has a number of advantages such as speed, simple storage (avoiding the need for complex database administration) and interoperability with other databases such as MySQL and PostgreSQL.

Creating an SQLite Database with PHP

An SQLite database can be opened or created using the PHP sqlite_open() function. This function accepts one mandatory and two optional arguments. The first argument is the database name (which, by convention, is given a .sqlite file extension). The second argument specifies optional UNIX file permission settings. The final argument represents an error message to display if the file cannot be opened.

The sqlite_open() function returns a database handle on success, or a boolean false value on failure. A memory resident database can be created by passing in the string :memory as the database file name argument.

SQLite databases are closed using the sqlite_close() function.

The following example opens a database called phptest.sqlite:

<?php
        $dbhandle = sqlite_open('phptest.sqlite');

        if ($dbhandle == false)
        {
                die ('Unable to open database');
        } else {
                echo 'Database created.';
        }
        sqlite_close($dbhandle)
?>

Using PHP to Add Records to an SQLite Database

Records are added to a SQLite database using the PHP sqlite_query() function. The ''sqlite_query() function takes two arguments, the first being the handle returned by the call to sqlite_open() and the second representing the SQL command to run on the database. Records are added using the SQL insert command:

<?php
        $dbhandle = sqlite_open('phptest.sqlite');

        if ($dbhandle == false)
        {
                die ('Unable to open database');
        } else {
                echo 'Database created.';
        }

        $dbquery = 'INSERT INTO customer (name, account) VALUES ("James Wilson", "12345678")';

        $dbresult = sqlite_query($dbhandle, $dbquery);

        sqlite_close($dbhandle)
?>

Using PHP to Select Records from an SQLite Database

As with adding records, selecting records simply consists of constructing a suitable SQL SELECT statement and passing it through to the sqlite_query() function:

<?php
        $dbhandle = sqlite_open('phptest.sqlite');

        if ($dbhandle == false)
        {
                die ('Unable to open database');
        } else {
                echo 'Database created.';
        }

        $dbquery = 'SELECT * FROM customer;

        $dbresult = sqlite_query($dbhandle, $dbquery);

        sqlite_close($dbhandle)
?>

The sqlite_query() function returns all the selected records. These can be extracted as an array of arrays using the sqlite_fetch_all() function which takes the handle returned from the query as an argument. Whilst this approach is fine for small amounts of data it tends to be inefficient when dealing with large data sets. It is preferable, in these circumstances, to use the sqlite_fetch_single() function in conjunction with the sqlite_has_more() function as follows:

<?php
        $dbhandle = sqlite_open('phptest.sqlite');

        if ($dbhandle == false)
        {
                die ('Unable to open database');
        } else {
                echo 'Database created.';
        }

        $dbquery = 'SELECT * FROM customer;

        $dbresult = sqlite_query($dbhandle, $dbquery);

        while (sqlite_has_more($dbresult))
        {
                $dbrow = sqlite_fetch_single($dbquery);
                print_r ($dbrow);
        }

        sqlite_close($dbhandle)
?>

Summary

This chapter has provided an overview of working with PHP and SQLite.