Examining a few more row-fetching methods - PHP

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!