PHP - MySQL min(),max() aggregate operations
Last Updated :
30 Sep, 2021
In this article, we are going to find minimum and maximum details from the table column using PHP from MySQL Xampp server. We are taking an example from employee database to find the minimum and maximum salary of the employee.
Requirements -Xampp server
Introduction :
- PHP -
It stands for Hyper Text Preprocessor which is used to create dynamic web pages. We are going to connect PHP to my SQL xampp server.
- xampp server -
It is a cross-platform which is used to store the databases locally.
- MySQL -
It is a query language which is used to manage the databases.
Min(), max() aggregate operations :
min() -
This is an aggregate function which is used to return the minimum value in the given sql expression. This can be used along with SELECT statement.
Syntax :
select min(column_name) as
minimum_column_name from table_name;
Here, as minimum_column_name is an optional one.
max() -
This is an aggregate function which is used to return the maximum value in the given SQL expression. This can be used along with SELECT statement.
Syntax :
select max(column_name) as maximum_column_name from table_name;
Here, as maximum_column_name is an optional one.
Approach :
- Create database (database) and table(salary) in xampp server.
- Write a code to insert details in a salary table using PHP.
- PHP's code to find the minimum and maximum salary using min() and max() functions.
- Observe the output on a web page.
Steps to start the server and storing data :
- Create a database named database and table salary in xampp. Type “localhost/phpmyadmin” in your browser.
PHP's code to insert records (data1.php) :
PHP
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
// Check this connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//insert records into table
$sql = "INSERT INTO salary VALUES ('sravan',35000,'IT');";
$sql .= "INSERT INTO salary VALUES ('sudheer',45000,'IT');";
$sql .= "INSERT INTO salary VALUES ('radha',25000,'MCA');";
$sql .= "INSERT INTO salary VALUES ('vani',35000,'BCA');";
if ($conn->multi_query($sql) === TRUE) {
echo "data stored successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Output :
localhost/data1.php
Records inserted in xampp server as follows.
| emp_name | salary | department |
|---|
| sravan | 35000 | IT |
| sudheer | 45000 | IT |
| radha | 25000 | MCA |
| vani | 35000 | BCA |
PHP code to find minimum salary(form.php) :
PHP
<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
//sql query to find minimum salary
$sql = "SELECT MIN(salary) FROM salary";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo "Minimum Salary :". $row['MIN(salary)'];
echo "<br />";
}
//close the connection
$conn->close();
?>
</body>
</html>
Output :
localhost/form.php
PHP code to find maximum salary :
(form.php)
PHP
<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
//sql query to find the maximum salary
$sql = "SELECT MAX(salary) FROM salary";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo "Maximum Salary :". $row['MAX(salary)'];
echo "<br />";
}
//close the connection
$conn->close();
?>
</body>
</html>
Output :
localhost/form.php
PHP code to display both minimum and maximum salary :
PHP
<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
//sql query to find minimum salary and maximum salary
$sql = "SELECT MIN(salary),MAX(salary) FROM salary";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo "Minimum Salary :". $row['MIN(salary)'];
echo "<br />";
echo "Maximum Salary :". $row['MAX(salary)'];
echo "<br />";
}
//close the connection
$conn->close();
?>
</body>
</html>
Output :
Similar Reads
PHP - MYSQL : sum() operation
Problem Statement: In this article, we are going to perform sum() aggregate operation on our database using PHP with xampp server. So we are considering the food_order database and perform database sum() operation. Requirements: xampp Introduction: PHP stands for hypertext preprocessor which is a se
3 min read
PHP-My SQL avg() aggregate function
In this article, we are going to find the average of the column in the SQL database using PHP in Xampp server. We are taking an example of a food database to find the average cost of all the items of food. Let's discuss it one by one. Requirements - Xampp server Introduction :Here, we will see the o
4 min read
PHP | mysqli_fetch_array() Function
The mysqli_fetch_array() function is used to fetch rows from the database and store them as an array. The array can be fetched as an associative array, as a numeric array or both. Associative arrays are the arrays where the indexes are the names of the individual columns of the table. On the other h
2 min read
PHP | MySQL ( Creating Table )
What is a table? In relational databases, and flat file databases, a table is a set of data elements using a model of vertical columns and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows. Creating a
3 min read
PHP | MySQL ORDER BY Clause
The ORDER BY Clause can be used along with the SELECT statement to sort the data of specific fields in an ordered way. It is used to sort the result-set in ascending or descending order. Syntax : The basic syntax of the Order By clause is - Implementation of the Order By Clause : Let us consider the
3 min read
PHP date() format when inserting into datetime in MySQL
This problem describes the date format for inserting the date into MySQL database. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The date can be stored in this format only. However, it can be used with any time format functions to change it and display it. When writin
3 min read
PHP | MySQL LIMIT Clause
In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count.The value of both the parameters can be zero or positive integers. Offset:It is used to specify the offset of the fir
3 min read
PHP - Mysql GROUP BY HAVING Clause
Problem Statement :In this article, we are going to perform database operations with GROUP BY HAVING operation through PHP through xampp server. In this scenario, we are going to consider the food database. Requirements :xampp server Introduction :PHP is a server side scripting language that can com
3 min read
PHP Array Functions
Arrays are one of the fundamental data structures in PHP. They are widely used to store multiple values in a single variable and can store different types of data, such as strings, integers, and even other arrays. PHP offers a large set of built-in functions to perform various operations on arrays.
7 min read
MySQL | Operator precedence
Operator precedence specifies the order in which operators are evaluated when two or more operators with different precedence are adjacent in an expression. For example, 1+2/3 gives the different result as compared to (1+2)/3. Just like all other programming languages C, C++, Java etc. MySQL also ha
3 min read