Using Unbuffered Queries and More with SQLite with PHP 5

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.

As the patient reader that I think you are, you’ll remember that over the first article I covered some of the most relevant methods included with SQLite. Many of them were focused on creating databases and tables, as well as running queries and fetching rows from a given result set. As you learned previously, SQLite is really easy to work with, as it boasts a fully-featured RDBMS system. This system is based primarily on the file system to create the corresponding database structure, although memory-based databases are also supported.

In addition to the handy features that you learned in the first article of the series, SQLite comes packaged with many more methods that can be truly helpful for working with unbuffered queries, counting and seeking rows, and finding insertion IDs. Even though the SQLite library has been integrated into a small package along with the PHP 5 distribution, I’m sure that you’ve already realized its remarkable capabilities.

Taking into account the group of characteristics that I mentioned a few lines above, in this second tutorial of the series, I’ll be taking a look at some of them. This will give you a clearer idea of how to use them as part of your existing and — why not? — future PHP applications.

Indeed, the subject looks interesting, therefore let’s no waste more time in preliminaries and continue discovering more handy methods included with SQLite. Let’s get going!

{mospagebreak title=Working with unbuffered queries}

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.

{mospagebreak title=Counting rows and fields of database tables}

Indeed, one of the most common tasks performed when working with a RDBMS is counting the number of rows returned by a concrete query. Particularly, SQLite makes this process really painless. It offers a new method called “numRows().” As the name suggests, it comes in handy for determining how many rows were returned by a result set.

Concerning the implementation of this method, below I coded an example that demonstrates how to use it:

// example using the ‘num_rows()’ method

// 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->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 />';

}

echo ‘Number of rows returned by the query: ‘. $result->numRows
();

/*

// displays the following:

1 User1 user1@domain.com

2 User2 user2@domain.com

3 User3 user3@domain.com

Number of rows returned by the query: 3

*/

As illustrated above, the respective “numRows()” method is called up after a proper result set object has been created, to calculate the number of rows returned by a SELECT statement. Also, you should notice the high level of intuitiveness exposed by this method, since it allows you to perform the row-counting operation as you’d normally do with a custom class. Definitely, here is where SQLite starts shining!

All right, now that you know how to count returned rows with the “numRows()” method that you saw before, let me show you another method. It will allow you to determine the number of fields contained in a given result set. Not surprisingly, this has been called “numFields()” and it can be used as follows:

// example using the ‘numFields()’ method

// 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->query("SELECT * FROM users");

// loop over rows of database table

while($row=$result->fetch(SQLITE_NUM)){

    // display row

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

}

echo ‘Number of fields in result set: ‘. $result->numFields();

/*

// displays the following

1 User1 user1@domain.com

2 User2 user2@domain.com

3 User3 user3@domain.com

Number of fields in result set: 3

*/

In this case, the above “numFields()” method is quite useful for determining how many fields were returned in a particular result set. As you’ll realize, this method is very similar to the previous “numRows()” method, since it’s also been attached to the $result data set object. Undoubtedly, after studying the two previous examples, you’ll agree with me that SQLite makes counting table rows and fields a no-brainer process!

So far, I demonstrated the implementation of some additional methods included with SQLite, which certainly will make your life much easier, particularly if the application you’re developing involves fairly intensive processing of result sets.

However, I’ve not come to the end of this tutorial yet. I’d like to show you a couple of extra methods which will add some handy capabilities to your PHP applications. These methods will help when it comes to determining specific information about fetched database rows.

Want to learn how to use these helpful methods? Click on the link below and keep reading.

{mospagebreak title=Analyzing more row-processing methods}

As I expressed in the previous section, I’d like to end this second article of the series by teaching you how to use a pair of new methods, obviously included within the SQLite library. The first method that I’ll show you here is called “fetchSingle()” and its functionality is limited to retrieving all the data that corresponds to only one row.

One possible implementation for this method is shown below. Take a look at the corresponding code listing: 

// example using the ‘fetchSingle()’ method

// 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->query("SELECT * FROM users");

// fetch first row in result set

$firstRow=$result->fetchSingle();

echo ‘Id of first column: ‘.$firstRow['id'];

/*

displays the following

Id of first column: 1

*/

In this case, the “fetchSingle()” method has been used to retrieve the ID corresponding to the first row of the returned result set. As with the other methods that I explained before, this one is called directly after the pertinent $result object has been instantiated. Simple, isn’t it?

Finally, the last example that I’ll show you concerns the implementation of the “fetchColumnTypes()” method, which as you may guess, can be really helpful for determining the type of fields defined for a specific database table. That said, here is how this new method can be properly used:

// example using the ‘fetchColumnTypes()’ method

// 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;");

$cols=$db->fetchColumnTypes(‘users’);

foreach($cols as $col=>$type){

            echo ‘Column ‘.$col.’ has the following type
‘.$type.'<br />';

}

/*

// displays the following:

Column id has the following type INTEGER

Column name has the following type CHAR(255)

Column email has the following type CHAR(255)

*/

In this case, aside from demonstrating the functionality of the above “fetchColumnTypes()” method, you should notice that it has been attached to an instance of the SQLite class, instead of being used in conjunction with a specific result set object. As you’ll realize, due to its rather limited functionality, the prior example speaks for itself, therefore it bears no much discussion.

All right, at this point I provided you with a neat group of methods bundled with the SQLite library to perform all sorts of clever tasks on file-based databases. As usual, feel free to tweak the code samples and introduce your own modifications to them.

Wrapping up

That’s all for the moment. In this second part of the series I covered some of the most important methods that come with SQLite, thus I hope the experience has been instructive and fun.

Nevertheless, this learning journey hasn’t finished yet. Over the final article, I’ll be taking a look at the remaining SQLite methods, which can be really useful for finding row insertion IDs, implementing iterators, defining custom functions, and more. Until then, stay tuned!

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