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
