Home arrow PHP arrow Page 2 - Using Unbuffered Queries and More with SQLite with PHP 5

Working with unbuffered queries - PHP

Are you one of those PHP developers looking for a tight and powerful RDBMS, other than MySQL, in order to build your next database-driven website? Then hopefully your search is finished. Welcome to the second installment of the series “Using SQLite with PHP 5.” Comprised of three articles, this series walks you through the implementation of the most important methods that come bundled with SQLite and shows you how to use them with numerous practical examples.

TABLE OF CONTENTS:
  1. Using Unbuffered Queries and More with SQLite with PHP 5
  2. Working with unbuffered queries
  3. Counting rows and fields of database tables
  4. Analyzing more row-processing methods
By: Alejandro Gervasio
Rating: starstarstarstarstar / 2
December 11, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: