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

Analyzing more row-processing methods - 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

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!



 
 
>>> 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: