Analyzing more row-processing methods - PHP

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!