Difference between revisions of "PHP and SQLite"

Revision as of 17:10, 7 June 2007

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 Datbase with PHP

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

The sqlite_open() returns a datbase handle on success, or a boolean false value on failure. A memory resiodent database can be created by passing in the string :memory as the datbase 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 teh 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 wiht large data sets. It is preferable, in these circumanstances, 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)
?>