The basics of SQLite - PHP

As I said right at the beginning, SQLite allows you to work with a full-featured relational database mechanism, with an important difference from MySQL. With MySQL, you would normally appeal to a separate server. With SQLite, the library uses the file system to create the corresponding sets of databases and tables. Memory-based databases are also fully supported by SQLite, but this topic will be covered in upcoming articles of the series.

To begin with, let me show you a simple example which demonstrates the complete process. This example is aimed at creating a sample database called “db.sqlite,” defining a “USERS” table, and finally fetching some rows from it using an object-oriented approach.

Here is the example in question, therefore take a look at it: 

// create new database using the OOP approach

$db=new SQLiteDatabase("db.sqlite");

// create table 'USERS' and insert sample data

$db->query("BEGIN;

        CREATE TABLE users (id INTEGER(4) UNSIGNED PRIMARY KEY,
name CHAR(255), email CHAR(255));

        INSERT INTO users (id,name,email) VALUES
(NULL,'User1','user1@domain.com');

        INSERT INTO users (id,name,email) VALUES
(NULL,'User2','user2@domain.com');

        INSERT INTO users (id,name,email) VALUES
(NULL,'User3','user3@domain.com');

        COMMIT;");

// fetch rows from the 'USERS' database table

$result=$db->query("SELECT * FROM users");

// loop over rows of database table

while($result->valid()) {

    // fetch current row

    $row=$result->current();

    print_r($row);

    // move pointer to next row

    $result->next();

}

As you’ll surely realize, the above code listing shows some new and interesting things that need to be properly explained with reference to the basic implementation of SQLite. First, notice how the “db.sqlite” database is created via the respective SQLite constructor, and then some trivial rows are inserted into the sample “USERS” table by the intuitive “query()” method.

You can see that the previous database has been defined on the file system (note the specification of the respective “db.sqlite” file). Additionally, the creation of the “USERS” table, as well as the insertion of sample rows, has been performed in a single step, since SQLite supports the transactional model.

Once the prior database table has been populated with basic data, rows are returned via the proper execution of a regular SELECT statement. Of course, it’s important to notice here how the “query()” methods return a result set object, which is used inside a “while” loop to display the corresponding database rows. These rows are shown below:

Array ( [0] => 1 [id] => 1 [1] => User1 [name] => User1 [2] =>
user1@domain.com [email] => user1@domain.com ) Array ( [0] => 2
[id] => 2 [1] => User2 [name] => User2 [2] => user2@domain.com
[email] => user2@domain.com ) Array ( [0] => 3 [id] => 3 [1] =>
User3 [name] => User3 [2] => user3@domain.com [email] =>
user3@domain.com )

Also, for this example, I used some additional methods, like “valid()”, “current()” and “next()” respectively, to traverse the mentioned result set, as I’d proceed with a regular array structure. Therefore, it’s clear to see here how the implementation of a data set iterator is only a matter of using the correct methods.

Right, at this point you hopefully learned how to create a simple database with SQLite, as well as how to define a database table and run some queries against it. That was really simple, wasn’t it? Now, it’s time to explore a few more handy methods that come embedded with this library.

To learn how these brand new methods will be defined, keep reading.