Counting rows and fields of database tables - PHP

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.