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

Counting rows and fields of database tables - 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

Indeed, one of the most common tasks performed when working with a RDBMS is counting the number of rows returned by a concrete query. Particularly, SQLite makes this process really painless. It offers a new method called “numRows().” As the name suggests, it comes in handy for determining how many rows were returned by a result set.

Concerning the implementation of this method, below I coded an example that demonstrates how to use it:

// example using the 'num_rows()' 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");

// loop over rows of database table

while($row=$result->fetch(SQLITE_NUM)){

    // fetch current row

    echo $row[0].' '.$row[1].' '.$row[2].'<br />';

}

echo 'Number of rows returned by the query: '. $result->numRows
();

/*

// displays the following:

1 User1 user1@domain.com

2 User2 user2@domain.com

3 User3 user3@domain.com

Number of rows returned by the query: 3

*/

As illustrated above, the respective “numRows()” method is called up after a proper result set object has been created, to calculate the number of rows returned by a SELECT statement. Also, you should notice the high level of intuitiveness exposed by this method, since it allows you to perform the row-counting operation as you’d normally do with a custom class. Definitely, here is where SQLite starts shining!

All right, now that you know how to count returned rows with the “numRows()” method that you saw before, let me show you another method. It will allow you to determine the number of fields contained in a given result set. Not surprisingly, this has been called “numFields()” and it can be used as follows:

// example using the 'numFields()' 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");

// loop over rows of database table

while($row=$result->fetch(SQLITE_NUM)){

    // display row

    echo $row[0].' '.$row[1].' '.$row[2].'<br />';

}

echo 'Number of fields in result set: '. $result->numFields();

/*

// displays the following

1 User1 user1@domain.com

2 User2 user2@domain.com

3 User3 user3@domain.com

Number of fields in result set: 3

*/

In this case, the above “numFields()” method is quite useful for determining how many fields were returned in a particular result set. As you’ll realize, this method is very similar to the previous “numRows()” method, since it’s also been attached to the $result data set object. Undoubtedly, after studying the two previous examples, you’ll agree with me that SQLite makes counting table rows and fields a no-brainer process!

So far, I demonstrated the implementation of some additional methods included with SQLite, which certainly will make your life much easier, particularly if the application you’re developing involves fairly intensive processing of result sets.

However, I’ve not come to the end of this tutorial yet. I’d like to show you a couple of extra methods which will add some handy capabilities to your PHP applications. These methods will help when it comes to determining specific information about fetched database rows.

Want to learn how to use these helpful methods? 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: