Getting started

resource sqlite_open ( string filename [, int mode [, string &error_message]])

void sqlite_close ( resource db_handle)

resource sqlite_query ( resource db_handle, string query)

array sqlite_fetch_array ( resource result [, int result_type [, bool decode_binary]])

Working with SQLite is, from a code perspective, almost exactly the same as working with other databases. The syntax is a little different, and you invariably need to pass in an exact database connection with each call to the library, however there should be no problem if you have already mastered another SQL dialect.

Author's Note: There's also an object-oriented version of SQLite for people who like that sort of thing.

The four key functions to use are sqlite_open(), sqlite_close(), sqlite_query(), and sqlite_fetch_array(), and work almost exactly like their MySQL equivalents - note, though, that the connection function is sqlite_open(), not sqlite_connect(), reflecting the lack of client/server architecture.

Here is an example script:

<?php
    $dbconn
= sqlite_open('phpdb');

    if (
$dbconn) {
        
sqlite_query($dbconn, "CREATE TABLE dogbreeds (Name VARCHAR(255), MaxAge INT);");
        
sqlite_query($dbconn, "INSERT INTO dogbreeds VALUES ('Doberman', 15)");
        
$result = sqlite_query($dbconn, "SELECT Name FROM dogbreeds");
        
var_dump(sqlite_fetch_array($result, SQLITE_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 to sqlite_open. 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 sqlite_fetch_assoc() function at this time, so the sqlite_fetch_array() function is used, specifying SQLITE_ASSOC as parameter two. If you do not do this, sqlite_fetch_array() 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.

Note that the last parameter to sqlite_fetch_array, "decode_binary", is set to true by default and should be left that way unless you need to work with data from other SQLite applications that require a specific value for this setting.

Author's Note: When calling sqlite_open(), you can pass in ":memory:" as the filename to have SQLite create its database in memory - this is substantially faster than working with a 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.

 

Next chapter: Advanced functions >>

Previous chapter: Before you begin

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar