How to count rows in MySQL table in PHP ?

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:

• XAMPP

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 );

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.

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

• Write PHP code to count rows.

PHP code:

<?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

// 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