How to Count Rows in MySQL PHP

1. Use fetchColumn() Method of PDO to Count the Total Number of Rows in a MySQL Table
2. Use a Procedural Method to Count the Number of Rows in the MySQL Table Using the mysqli_num_rows() Function
3. Use an Object-Oriented Way to Count the Number of Rows in a Table Using the num_rows Property

We will introduce a method to count all the rows from a table in MySQL in PHP using the fetchColumn() and COUNT() methods. The fetchColumn() function is given by PDO(PHP Data Objects) and the COUNT() method is a SQL function. PDO is an object-oriented way of connecting the database and the backend. This method is flexible because PDO is compatible with 12 different database systems.

We will demonstrate another method to count all rows from a table using the mysqli_num_rows() function. This method uses an object-oriented way to establish the database connection with the server using the mysqli() function. However, the rest of the process is done procedurally.

We will demonstrate a way to count all the rows from a table using PHP’s num_rows property. This method follows the complete object-oriented practice to connect the database and count the total number of rows. The use of the prepared statement in this method makes it safe from the vulnerability of SQL injection.

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 ensure 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();
$testObj->getRowsNumber()

Example Code:

# php 7.*
<?php
class Test extends DB {
    public function getRowsNumber() {
        $sql = "SELECT COUNT(*) FROM users";
        $stmt = $this->connect()->query($sql);
        $count = $stmt->fetchColumn();
        print $count;
    }
}

Output:

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
<?php
$conn = new mysqli($host, $username, $password, $database);
$sql = "SELECT * FROM users";
if ($result=mysqli_query($conn,$sql)) {
    $rowcount=mysqli_num_rows($result);
    echo "The total number of rows are: ".$rowcount; 
}
?>

Output:

The total number of rows are: 2

Use an Object-Oriented Way to Count the Number of Rows in a Table Using the num_rows Property

We can use the num_rows property in PHP to count the number of rows in a MySQL table. This approach uses the object-oriented method. The method is quite similar to the second method in creating the database connection and writing the SQL query. The use of prepared statements differs this method from the second one. We use prepare() function to create a prepared statement, execute() function to execute the prepared statement and store_result() function to store the result.

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 $stmt to store the prepared statement. Use $conn vairable to call the prepare() function which takes the sql variable as the parameter. Check the boolean value of the $stmt variable using if condition. Inside the if condtion, use the $stmt variable to call the execute() function and then the store_result() function . And then, call the num_rows property and print it.

Example Code:

#php 7.x
<?php
$conn = new mysqli($host, $username, $password, $database);
$sql = "SELECT * FROM users";
if ($stmt = $conn->prepare($sql)) {
    $stmt->execute();
    $stmt->store_result();
    printf("Number of rows: %d.\n", $stmt->num_rows);
}
?>

Output:

Number of rows: 2.