Retrieving rows from a database table - PHP

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.