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
