How to Select Count Function From a MySQL Table in PHP

John Wachira Feb 02, 2024
  1. Count Rows in a MySQL Table With the Select Count(*) Function in PHP
  2. Display the Total Records Returned by a Query in a MySQL Table With the Select Count(*) Function in PHP
How to Select Count Function From a MySQL Table in PHP

This tutorial will introduce the select count(*) function, count rows, and get the total records returned by a query from a MySQL table in PHP. Together with the tutorial are working sample codes and their output.

The first step is connecting to the MySQL table database in PHP. Below is a sample code we use to connect to our database.

Example code 1:

<?php

$user = 'root';
$pass = '';
$db = 'sample tutorial';
//Replace 'sample tutorial' with the name of your database

$con = mysqli_connect("localhost", $user, $pass, $db);

//The code does not have an output, so we decided to print 'Database Connected'
echo "Database Connected";

?>

Remember to replace sample tutorial with the name of the database containing your MySQL table.

The above code usually displays no output when successfully connected. We decided to print Database Connected as the optional output in our case.

The table in our database is as follows:

CarID BrandName OwnersName RegistrationNumber
1 Benz John KDD125A
2 Porsche Ann KCK345Y
3 Buggatti Loy KDA145Y
4 Audi Maggie KCA678I
5 Filder Joseph KJG998U
6 Tesla Tesla KMH786Y

Count Rows in a MySQL Table With the Select Count(*) Function in PHP

Below is a sample code to count rows in a MySQL table using PHP’s select count(*) function.

Example code 2:

<?php

$user = 'root';
$pass = '';
$db = 'sample tutorial';

//Replace 'sample tutorial' with the name of your database

$con = mysqli_connect("localhost", $user, $pass, $db);

$sql = "SELECT * FROM parkinglot1";

$result = mysqli_query($con, $sql);

$num_rows = mysqli_num_rows($result);

printf("Number of rows in the table : %d\n", $num_rows);

?>

Output:

Number of rows in the table : 6

Display the Total Records Returned by a Query in a MySQL Table With the Select Count(*) Function in PHP

The select count(*) function can get the total records returned by a query in the table in our database.

Below is a sample code to display the total records in the BrandName category.

Example code 3:

<?php

$user = 'root';
$pass = '';
$db = 'sample tutorial';

$con = mysqli_connect("localhost", $user, $pass, $db);

$sql = "SELECT COUNT(BrandName) AS total FROM parkinglot1";

$result = mysqli_query($con, $sql);

$data = mysqli_fetch_assoc($result);

echo $data['total']

?>

Output:

6

Remember to replace the sample tutorial with the name of the database containing your MySQL table.

Another sample code and output for the OwnersName category.

Example code 4:

<?php

$user = 'root';
$pass = '';
$db = 'sample tutorial';

$con = mysqli_connect("localhost", $user, $pass, $db);

$sql = "SELECT COUNT(OwnersName) AS total FROM parkinglot1";

$result = mysqli_query($con, $sql);

$data = mysqli_fetch_assoc($result);

echo $data['total']

?>

Output:

6

We use SELECT * FROM parkinglot1 to count rows and SELECT COUNT(*) AS total FROM parkinglot1 to display the total records returned by a query from our MySQL table.

Author: John Wachira
John Wachira avatar John Wachira avatar

John is a Git and PowerShell geek. He uses his expertise in the version control system to help businesses manage their source code. According to him, Shell scripting is the number one choice for automating the management of systems.

LinkedIn

Related Article - PHP Table