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

Retrieving rows from a database table - 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

Undoubtedly, one of the most common tasks performed when using a RDBMS is fetching rows from one or many database tables. SQLite has some helpful methods that will let you do this with only minor hassles. However, when it comes to retrieving records from a specific database table, the “fetch()” method is by far the one most used, thanks to its extreme simplicity and functionality.

Now, with reference to the above mentioned method, below I coded a pair of examples that show how to use it, in conjunction with its associated constants. Thus, have a look at the following pair of code listings:

// example using 'fetch()' method and SQLITE_ASSOC constant

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

*/

// example using 'fetch()' method and SQLITE_NUM constant

// 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->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 first example shows in an accessible way how the previous “fetch()” method can be used along with its “SQLITE_ASSOC” constant to retrieve rows from a specified database table as an associative array. This feature is closely similar to the “mysql_fetch_row()” PHP built-in function that you’ve used probably hundreds of times.

Concerning the second example, the corresponding “fetch()” method is utilized this time in conjunction with the “SQLITE_NUM” constant. This comes in very handy for obtaining database rows by using only their numeric keys. 

Of course, I’m sure that the couple of examples you learned before are pretty familiar to you, since they are very similar to the respective PHP functions used for fetching rows from MySQL. You'll hear that refrain a lot in reference to all the methods that I’ll be reviewing during the course of this series.

Okay, now that you hopefully grasped the logic that stands behind the “fetch()” method, let’s take an in-depth look at other useful methods. In the following section I’ll show you how to use a pair of new methods to fetch all the rows from a particular result set. You'll also learn how to retrieve records by using an object-based notation.

Wan to learn how this will be done? Please, click on the link below and keep reading.



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