User Tools

Site Tools


db:sqlite_small_tutorial

SQLite

Introduction to SQLite
27 March 2008

What is SQLite?

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. More information on SQLite website.

SQLite library is already included in PHP5, so you must enabled it in php.ini. Check the php website or other sources on the internet to see the installation procedure.

First steps.

So, let's begin our journey…
Let's suppose we have a folder called 'test' somewhere in the webserver document root and we make a script inside called 'index.php'.

We create then the database:

    $db = new SQLiteDatabase("test.db") or die("Could not open 	database"); 

which anyway is similar to 'sqlite_factory' function:

    $db = sqlite_factory(“test.db”);

but we'll stick with the OO style.

Next we create a table using 'query' method:

    $db->query("CREATE TABLE users (id INTEGER PRIMARY KEY,name CHAR(20), email CHAR(50))");

We created the first field 'integer primary key' because a column defined this way is with autoincrement. The value for this field should be inserted as NULL as you see below.

Inserting some data

$db->query("BEGIN;
    INSERT INTO users (id, name, email) VALUES (NULL, 'John Doe', 'john@example.org');
    INSERT INTO users (id, name, email) VALUES (NULL, 'Rox Cox', 'rcox@example.org');
    INSERT INTO users (id, name, email) VALUES (NULL, 'Jonny Daemon', 'jonnyd@example.org');
    COMMIT;
    ");

Fetching the data

So, we have a table with some data inside. Let's try to retrieve its data:

$result = $db->arrayQuery('SELECT * FROM users',   SQLITE_ASSOC);
 
foreach ( $result as $res ) {
    echo 'ID: ' .$res['id']. ' NAME: ' .$res['name']. ' EMAIL: ' .$res['email'] .'<br />';
}

We're using here arrayQuery method but this is best suited only for 45 rows or less. See link for more informations. If we have more rows, then we should use $db→query() instead.

Because the arrayQuery returned an array, we can use foreach to iter through every item.

What about if we had been using the query method. The code would look like:

$result = $db->query('SELECT * FROM users');
 
while ( $result->valid() ) {
    $row = $result->current();
    echo 'ID: ' .$row['id']. ' NAME: ' .$row['name']. ' EMAIL: ' .$row['email'] .'<br />';
    $result->next();
}

In a similar way, we can use 'fetchAll' method (who will return an array of arrays).

$result = $db->query('SELECT * FROM users');
 
$data = $result->fetchAll(SQLITE_ASSOC);
foreach ( $data as $res ) {
    echo 'ID: ' .$res['id']. ' NAME: ' .$res['name']. ' EMAIL: ' .$res['email'] .'<br />';
}

Another way to do that is (we also check if there are any results at all):

if ( $result->numRows() > 0) {
    while ( $row = $result->fetch() ) {
        echo 'ID: ' .$row['id']. ' NAME: ' .$row['name']. ' EMAIL: ' .$row['email'] .'<br />';
    }
}

So, if you want to retrieve all the results at one moment, or every line it's up to you, function of your goals.

Closing the connection

unset($db);

in a procedural programming style, you should write:

sqlite_close($db);

db/sqlite_small_tutorial.txt · Last modified: 2013/03/16 17:40 (external edit)