Verification: a143cc29221c9be0

No of rows fetched in php

Use fetchColumn() Method of PDO to Count the Total Number of Rows in a MySQL Table

PDO is one of the object-oriented ways to connect the database with the PHP server. We can use the fetchColumn() method available in PDO to count the table’s rows. Firstly, we create a database and populate the table with data. Then, we set up the database connection using the correct host, database username, database password, and database name. We use an instance of a PDO object to store the connection. Once we make sure the database connection is working, we query the SQL statement using the COUNT() function and execute it. The COUNT() function takes the * as the parameter that counts all rows in the specified table. Then we use the fetchColumn() method to display the number of rows in the table.

The instructions below assume that a database connection with the server is established. There are two rows in the table in the database.

MariaDB [oop]> select * from users;
| id | firstname | lastname   | dob        |
|  1 | Dan    	 |  James     | 1998-08-23 |
|  2 | Dean   	 | Henderson  | 2000-03-30 |
2 rows in set (0.003 sec)

For example, in Test.php write a class named Test that inherits the DB class. Write a public function getRowsNumber() inside the class. Assign a variable $sql and write a query using the SELECT statement to select everything from the users table. Use COUNT(*) in the SELECT statement to count the number of rows. Use the connect function to get the connection from DB.php and query the above-written SQL command using the query() function. Assign these tasks to a $stmt variable. Use $stmt variable to call the fetchColumn() method and display the result.

In the example below, the superclass DB contains a connect() method which contians the database connection. The getRowsNumber() function is invoked from anothe php file as:

$testObj = new Test();

Example Code:

# php 7.*
        $count = $stmt->fetchColumn();
        print $count;


The total number of rows is: 2

Use a Procedural Method to Count the Number of Rows in the MySQL Table Using the mysqli_num_rows() Function

We can use the mysqli_num_rows() function in PHP to count the rows in a MySQL table. We can create an object of the mysqli() function to connect the database with the PHP server. The function takes hostname, username, password, and database name as parameters. We write the SQL statement to select every row from the table. We use the mysqli_query() function to use the database connection and execute the query. Then we use the mysql_num_rows() function to count the number of rows and display it.

The example below uses the same database, the same table, and the same data in the table as in the first method.

For example, assign the hostname, username, password, and database name to the variables $host, $username, $password, and $database, respectively. Create an object of the mysqli() function using the new keyword and pass the variables as the function’s parameters. Assign the value of the object in the $conn variable. Write a SQL query to select everything from the users table in the $sql variable. Use a variable $result to store the mysqli_query() function which takes the $conn and the $sql variables. Check the boolean value of the $result variable using the if condition. Inside the if condition use mysqli_num_rows() with $request variable as the parameter and assign it to $rowcount variable. Print the $rowcount variable.

Code Example:

#php 7.x


The total number of rows are: 2