Working with unbuffered queries - PHP

Before I proceed to demonstrate how to use unbuffered queries with SQLite, first let me introduce a brief explanation of what they are, so you can use them more consciously inside your PHP code. By default, when a result set is obtained from a specified database, SQLite will keep it in memory (the buffer), in this way allowing the execution of other useful tasks, like counting the number of rows returned by a query.

Obviously, this benefit comes at a cost, since extra server memory will be used for this purpose. However, if you don’t need to determine the amount of rows contained in a result set, you may want to use the “unbufferedQuery()” method. It skips over the buffering process and simply saves the corresponding data set onto a new object.

After noting the difference between buffered and unbuffered queries, here’s how to use this new method. Look at the example below:

// example using 'unbufferedQuery()' method and SQLITE_ASSOC
constant

// create new database using the OOP approximation

$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->unbufferedQuery("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

*/

As shown in the above example, the “unbufferedQuery()” method can be used in a nearly identical fashion to its counterpart “query().” Its main difference rests on the absence of the mentioned buffered result set, which otherwise would be created after executing a SELECT statement.

As explained previously, this limitation won’t let you determine, at least directly, the number of rows contained in a result set, but if you don’t need to have this functionality, the “unbufferedQuery()” method can slightly improve the overall performance of your application.

Now, returning to the previous example, you can see the method has been used in conjunction with “fetch(),” which retrieves all the database rows as an associative array, due to the specification of the SQLITE_ASSOC constant.

Therefore, considering the constants that can be assigned to the “fetch()” method, it’s possible to set up yet another example that demonstrates how the “unbufferedQuery()” method can be correctly implemented to fetch database rows as a numerically-indexed array. More specifically, this can be coded as follows:

// example using 'unbufferedQuery()' method and SQLITE_NUM
constant

// create new database using the OOP approximation

$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->unbufferedQuery("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 above example is closely similar to the previous one. The only difference rests on the way that database rows are fetched from the returned result set. As you saw, in this case the SQLITE_NUM constant was specified as an argument of the “fetch()” method, thus database results are retrieved by their numeric keys. Short and simple, right?

At this level, and after learning the pros and cons of using the “unbufferedQuery()” method, it’s good to move on and keep covering other handy methods that have been packaged with SQLite.

More specifically, in the following section I’ll show you how to count the number of rows and fields contained in a given result set, which means that you should go ahead an read the next few lines. I’ll be there, waiting for you.