Introduction to Using SQLite with PHP 5 - Examining a few more row-fetching methods
(Page 4 of 4 )
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!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
