How to Count Rows in MySQL PHP
-
Use
fetchColumn()
Method of PDO to Count the Total Number of Rows in a MySQL Table -
Use a Procedural Method to Count the Number of Rows in the MySQL Table Using the
mysqli_num_rows()
Function -
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.
Subodh is a proactive software engineer, specialized in fintech industry and a writer who loves to express his software development learnings and set of skills through blogs and articles.
LinkedIn