Getting started with SQLite 3

void sqlite3::close ()

resource sqlite3::query ( string query)

array sqlite3result::fetchArray ( [int mode = SQLITE3_BOTH])

Working with SQLite is, from a code perspective, almost exactly the same as working with other databases. The syntax is a little different, and it uses object-oriented style in PHP, however there should be no problem if you have already mastered another SQL dialect.

The four key SQLite actions are identical to MySQL: opening a database, closing, plus running SQL and fetching results. Note that SQLite uses a local file for its data store, so you open and close files rather than connect and disconnect from a server.

Here is an example script:

<?php
    $db = new SQLite3('phpdb');

    if ($db) {
        $db->query("CREATE TABLE dogbreeds (Name VARCHAR(255), MaxAge INT);");
        $db->query("INSERT INTO dogbreeds VALUES ('Doberman', 15)");
        $result = $db->query("SELECT Name FROM dogbreeds");
        var_dump($result->fetchArray(SQLITE3_ASSOC));
    } else {
        print "Connection to database failed!\n";
    }
?>

Notice that connecting to an SQLite database is simply a matter of providing the filename to use as the parameter when creating a new SQLite3 object. Some programmers have adopted the convention of using the filename extension ".sqlite" for their databases, but you are free to do as you please as this convention has yet to really catch on.

After opening the database, you will notice that sending queries requires passing the database connection as the first parameter, with the query as the second parameter. The queries themselves are standard SQL, so you should be able to take your existing SQL skill set and apply it directly here.

Finally, note that there is no fetchAssoc() method at this time, so SQLite's fetchArray() method is used, specifying SQLITE3_ASSOC as parameter two. If you do not do this, fetchArray() will return each field of data twice - once with its numeric index, and again with its field name string index.

Other than the minor differences listed above, SQLite works much like MySQL, as you can see. The advantage of absolute cross-platform compatibility, regardless of whether people have a database server running, is a real treasure, and makes SQLite a great tool to keep handy in all programmers' toolkits.

Author's Note: When creating a new SQLite3 object, you can pass in ":memory:" as the filename to have SQLite create its database in memory - this can be substantially faster than working with a database on disk, but it will of course be deleted automatically when your script terminates. Nevertheless, if you have temporary calculations or want to do complex filtering, this is the best way to do it.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Advanced usage: SQLite3::lastInsertRowID() and SQLite3::querySingle() >>

Previous chapter: Before you begin

Jump to:

Home: Table of Contents