Introduction to Using SQLite with PHP 5

If you use PHP 5 to build web-based applications that access a database, you might want to consider SQLite. A "lighter" option than MySQL, this database may still suit your purposes if you don’t need all of MySQL’s bells and whistles. This three-part series covers many of SQLite’s most useful features.

Introduction

On today’s Web it is quite common to find websites that use some kind of database as an appropriate backend for housing their dynamic content. This content will be served to disparate site visitors by using either a procedural or an object-based application layer in conjunction with the corresponding presentation mechanism.

Although the situation that I described above seems to grow increasingly common as the Web expands its apparently endless boundaries, I have to admit that this topic isn’t exactly the subject of this series. Instead, the question that I’d like to ask is the following: how many times during your life as PHP developer have you found yourself wishing to have at your disposal an RDBMS other than MySQL to fit your data storage requirements?

Indeed, if you’re working on a particular web project that isn’t too demanding in terms of using a full-featured relational database system, the answer to the previous question should be "frequently." If the neatly-crafted PHP application that you’re currently developing needs to use an RDBMS, but doesn’t require the utilization of the numerous, and sometimes overwhelming features offered by MySQL, I have good news for you.

If you’ve been using PHP 5 for a while, maybe you’ve heard several comments about the cool RDBMS called “SQLite,” which comes embedded with the latest incarnation of the language. This feature is truly handy, since there’s no need to use a separate piece of software (like MySQL for instance) to build a database application.

To be a little bit more specific concerning the topic, SQLite will let you perform most of the tasks you used to achieve with MySQL, like running buffered and unbuffered queries against a selected database, processing result sets in all sorts of clever ways, counting and seeking rows, and much more, by using only a group of intuitive methods (or functions, when using a procedural approach). Isn’t this great?

Considering the numerous and handy features that come with the SQLite RDBMS, in this three-part series I’ll be introducing some of the most relevant ones. In this way you can start using them as part of your PHP 5-based applications.

However, I’d like to clarify one more thing before diving into the subject: all the hands-on examples that you’ll learn here will use the object-based API included with this library. If you’re inclined to work with a procedural method, I suggest you take a look at the PHP manual to cover in detail this specific approach.

With the preliminaries out of our way, it’s time to start discovering many of the useful methods that come bundled with SQLite. Let’s do it together!

{mospagebreak title=The basics of SQLite}

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.

{mospagebreak title=Retrieving rows from a database table}

Undoubtedly, one of the most common tasks performed when using a RDBMS is fetching rows from one or many database tables. SQLite has some helpful methods that will let you do this with only minor hassles. However, when it comes to retrieving records from a specific database table, the “fetch()” method is by far the one most used, thanks to its extreme simplicity and functionality.

Now, with reference to the above mentioned method, below I coded a pair of examples that show how to use it, in conjunction with its associated constants. Thus, have a look at the following pair of code listings:

// example using ‘fetch()’ method and SQLITE_ASSOC constant

// 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($row=$result->fetch(SQLITE_ASSOC)){

    // fetch current row

    echo $row['id'].’ ‘.$row['name'].’ ‘.$row['email'].'<br />';

}

/*

//displays the following:

1 User1 user1@domain.com

2 User2 user2@domain.com

3 User3 user3@domain.com

*/

// example using ‘fetch()’ method and SQLITE_NUM constant

// 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($row=$result->fetch(SQLITE_NUM)){

    // fetch current row

    echo $row[0].’ ‘.$row[1].’ ‘.$row[2].'<br />';

}

/*

//displays the following:

1 User1 user1@domain.com

2 User2 user2@domain.com

3 User3 user3@domain.com

*/

As you can see, the first example shows in an accessible way how the previous “fetch()” method can be used along with its “SQLITE_ASSOC” constant to retrieve rows from a specified database table as an associative array. This feature is closely similar to the “mysql_fetch_row()” PHP built-in function that you’ve used probably hundreds of times.

Concerning the second example, the corresponding “fetch()” method is utilized this time in conjunction with the “SQLITE_NUM” constant. This comes in very handy for obtaining database rows by using only their numeric keys. 

Of course, I’m sure that the couple of examples you learned before are pretty familiar to you, since they are very similar to the respective PHP functions used for fetching rows from MySQL. You’ll hear that refrain a lot in reference to all the methods that I’ll be reviewing during the course of this series.

Okay, now that you hopefully grasped the logic that stands behind the “fetch()” method, let’s take an in-depth look at other useful methods. In the following section I’ll show you how to use a pair of new methods to fetch all the rows from a particular result set. You’ll also learn how to retrieve records by using an object-based notation.

Wan to learn how this will be done? Please, click on the link below and keep reading.

{mospagebreak title=Examining a few more row-fetching methods}

As I expressed at the end of the previous section, I’d like to show you a couple of additional methods that come bundled with SQLite. These may be useful when you want to retrieve all the rows contained in a result set in a single step. They may also be useful for retrieving database records by using an object-based notation.

Here is the first hands-on example which teaches you how to use the brand-new “fetchAll()” method, obviously aimed at obtaining all the rows present in a given result set. The corresponding code sample looks like this:

// example using the ‘fetchAll()’ method

// create new database using the OOP approach

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

// create table ‘USERS’ and insert sample data

$result=$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");

$rows=$result->fetchAll();

foreach($rows as $row){

   echo ‘Id: ‘.$row['id'].’  Name: ‘.$row['name'].’ Email: ‘.$row
['email'].'<br />';

}

/*

// displays the following:

Id: 1 Name: User1 Email: user1@domain.com

Id: 2 Name: User2 Email: user2@domain.com

Id: 3 Name: User3 Email: user3@domain.com

*/

Definitely, you’ll agree with me that the above example is very easy to grasp! Notice how the previous script uses the referenced “fetchAll()” method to retrieve, in one single pass, all the rows contained in the respective result set. After performing this process, database rows are displayed to the browser by using a regular “foreach” construct, since the method in question returns the corresponding records as an associative array. Quite simple, right?

Finally, the last example that I plan to show you here consists of a simple implementation of another useful method called “fetchObject().” As you’ll suppose, it is capable of retrieving rows from a specified data set by using an object-based notation. The respective code sample is listed below:

// example using ‘fetchObject()’ method

// 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($row=$result->fetchObject()){

    // fetch current row

    echo $row->id.’ ‘.$row->name.’ ‘.$row->email.'<br />';

}

/*

displays the following

1 User1 user1@domain.com

2 User2 user2@domain.com

3 User3 user3@domain.com

*/

As you’ll realize in this particular case, the above “fetchObject()” method behaves closely similar to its cousin “fetch().” It’s also capable of retrieving the rows coming from a concrete result set, but this time using an object-oriented syntax, where each field name is evaluated as an object property.

Finally, regarding the method that I explained before, if you’ve ever used the “mysql_fetch_object()” PHP built-in function, then you’ll find this one extremely familiar. Therefore, you shouldn’t have much trouble implementing it as part of your PHP applications.

To wrap up

Unfortunately, we’ve came to the end of this first article. In this tutorial, I walked you through the usage of the handy SQLite RDBMS that comes bundled with PHP 5. As you saw, creating databases and tables, running queries and fetching rows are all tasks that can be performed by using a few comprehensive methods.

However, this journey has just began, since SQLite comes packaged with many other methods that deserve special analysis, something that I’ll be covering over the course of the next article. Until then, stay tuned!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan