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