Home arrow PHP arrow Page 4 - Introduction to Using SQLite with PHP 5

Examining a few more row-fetching methods - PHP

If you use PHP 5 to build web-based applications that access a database, you might want to consider SQLite. A "lighter" option than MySQL, this database may still suit your purposes if you don't need all of MySQL's bells and whistles. This three-part series covers many of SQLite's most useful features.

TABLE OF CONTENTS:
  1. Introduction to Using SQLite with PHP 5
  2. The basics of SQLite
  3. Retrieving rows from a database table
  4. Examining a few more row-fetching methods
By: Alejandro Gervasio
Rating: starstarstarstarstar / 26
December 04, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



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