Most often, we want to count the number of rows found in our table from the database. It might be that we want to count the number of customers found in a certain city. Or it might be that you want to count if a certain username and password exist in a table. Or you want to count all students enrolled for every program.
There are two ways on how to achieve this. Actually, there are three. Let’s define a problem first and solve it using the three different ways. I will provide you with the files for you easily try it later on.
Problem: Create a program to count the number of customers from Silay City.
First, you need to create your database and create a table to be used in this activity. I provided the sql file in this link.
So here is the sample data found in the database I provided.
Database name: pos
Table name: customer
SQL COUNT FUNCTION
Let’s discuss the first way to solve the problem. First is with the use of the SQL COUNT aggregate function. Since we have a specific condition which is to count only those from Silay City, therefore we need to add the WHERE clause at the end of our SQL query.
PHP and MySQLi Code to count number of rows using SQL COUNT function
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Customer | PHP ADDICT</title>
</head>
<body>
<?php
$con = mysqli_connect("localhost","root","","pos");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$query=mysqli_query($con,"select COUNT(*) as total from customer where cust_city='Silay City'")or die(mysqli_error());
$row=mysqli_fetch_array($query);
?>
<h1>The number of customers living in Silay City is <?php echo $row['total'];?></h1>
</body>
</html>
MYSQLI_NUM_ROWS()
Second is with the use of mysqli_num_rows(). Still the output is the same with the first way.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Customer | PHP ADDICT</title>
</head>
<body>
<?php
$con = mysqli_connect("localhost","root","","pos");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$query=mysqli_query($con,"select * from customer where cust_city='Silay City'")or die(mysqli_error());
$count=mysqli_num_rows($query);
?>
<h1>The number of customers living in Silay City is <?php echo $count;?></h1>
</body>
</html>
PHP COUNT
<?php
$con = mysqli_connect("localhost","root","","pos");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$query=mysqli_query($con,"select * from customer where cust_city='Silay City'")or die(mysqli_error());
$count=0;
while($row=mysqli_fetch_array($query))
{
$count=$count+1;
}
?>
Goodluck! ^_^
OUTPUT for COUNT NUMBER OF ROWS IN DATABASE
No comments:
Post a Comment