Open In App

How to count rows in MySQL table in PHP ?

Improve
Improve
Like Article
Like
Save
Share
Report

PHP stands for hypertext preprocessor. MySQL is a database query language used to manage databases.

In this article, we are going to discuss how to get the count of rows in a particular table present in the database using PHP and MySQL.

Requirements:

Approach: By using PHP and MySQL, one can perform database operations. We can get the total number of rows in a table by using the MySQL mysqli_num_rows() function.

Syntax:

mysqli_num_rows( result );

The result is to specify the result set identifier returned by mysqli_query() function.

Example: The following table has 5 rows.

To count the number of rows in the building table, the following code snippet is used.

$sql = "SELECT * from building";

if ($result = mysqli_query($con, $sql)) {

    // Return the number of rows in result set
    $rowcount = mysqli_num_rows( $result );
    
    // Display result
    printf("Total rows in this table :  %d\n", $rowcount);
 }

Output: The expected result is as follows.

Total rows in this table : 5

Steps for the approach:

  • Create a database named database.
  • Create a table named building inside the database.
  • Insert records into it.
  • Write PHP code to count rows.

 

Steps:

  • Start XAMPP server.

XAMPP server

  • Create a database named database and create a table named building inside the database.
  • Insert records into it

building table

  • Write PHP code to count rows.

PHP code:

PHP




<?php
  
// localhost is localhost
// servername is root
// password is empty
// database name is database
$con = mysqli_connect("localhost","root","","database");
  
    // SQL query to display row count
    // in building table
    $sql = "SELECT * from building";
  
    if ($result = mysqli_query($con, $sql)) {
  
    // Return the number of rows in result set
    $rowcount = mysqli_num_rows( $result );
      
    // Display result
    printf("Total rows in this table : %d\n", $rowcount);
}
  
// Close the connection
mysqli_close($con);
  
?>


Output: After running the above PHP file in localhost, the following result is achieved.

Total rows in this table : 5

Example 2: In the following example, we count the table rows using MySQL count() function. It’s an aggregate function used to count rows.

Syntax:

select count(*) from table;

Consider the table.

PHP code:

PHP




<?php
  
// Servername
$servername = "localhost";
  
// Username
$username = "root";
  
// Empty password
$password = "";
  
// Database name
 $dbname = "database";
   
// Create connection by passing these
// connection parameters
$conn = new mysqli($servername
    $username, $password, $dbname);
   
// SQL query to find total count
// of college_data table
$sql = "SELECT count(*) FROM college_data ";
$result = $conn->query($sql);
  
// Display data on web page
while($row = mysqli_fetch_array($result)) {
    echo "Total Rows is ". $row['count(*)'];
    echo "<br />";
}
   
// Close the connection
$conn->close();
  
?>


Output:

Total Rows is 8


Last Updated : 26 May, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads