Max and Min Functions in MySQL

This article explains how we can calculate maximum and minimum value of all the values in a particular column.To do so, MAX() and MIN() functions are used in MySQL.

The MAX() function

SELECT MAX(column)
FROM table;

To find maximum of all the values in a particular column using MAX function in MySQLi, pass the query for finding maximum values to the query function of the mysqli object. The following example finds maximum of all the ages in the patient_age column of patient table.




Using MAX Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using MAX Function

$query = "SELECT MAX(patient_age) ".
"FROM Patient ";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Maximum age is " . $row["MAX(patient_age)"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

Using MIN() function in MySQL

The syntax of MIN function is as followsL

SELECT MIN(column)
FROM table;

To find minimum of all the values in a particular column using MIN function in MySQLi, pass the query for finding maximum values to the query function of the mysqli object. The following example finds minimum of all the ages in the patient_age column of patient table.




Using MIN Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using MIN Function

$query = "SELECT MIN(patient_age) ".
"FROM Patient ";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Minimum age is " . $row["MIN(patient_age)"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

SUM and AVG Functions in MySQL

MySQL contains several aggregate functions that are used for performing aggregate operations on specific column of a MySQL table. This article explains how we can calculate SUM and Average of all the values of the patient_age column of the patient table.

The SUM() function

SELECT SUM(column)
FROM table;

To add all the patient ages using SUM() function via MySQLi, pass the query for adding values to the query function of the mysqli object. The following example adds all values in the patient_age column.




Using SUM Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using SUM Function

$query = "SELECT SUM(patient_age) ".
"FROM Patient ";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Sum of Ages is " . $row["SUM(patient_age)"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

Using AVG() function in MySQL

SELECT AVG(column)
FROM table;

To calculate average of the patient ages using SUM() function in MySQLi, pass the query for averaging patient_age values to the query function of the mysqli object. The following example calculates average of all the values in the patient_age column.



Using Average Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using Average Function

$query = "SELECT AVG(patient_age) ".
"FROM Patient ";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Average of Age " . $row["AVG(patient_age)"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

Selecting Limited records via LIMIT in MySQL

Your table can contains thousands and thousands of records. However, seldom do we need to retrieve all the records. What if you want to display only top 100 records? MySQL allows you to do so via LIMIT query. The syntax of LIMIT query is as follows:

SELECT * FROM table
LIMIT no_of_records

Selecting Limited records via Object Oriented MySQLi

To select limited records via MySQLi, pass the query for selecting limited records to the query function of the mysqli object. The following example demonstrates this concept.




Using MySQLi for Selecting Limited Records



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Selecting limited data

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "LIMIT 5";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age columns of the top 5 records from Patient table of the hospital database.

Selecting Limited records via Procedural MySQLi

Selecting limited table data via procedural MySQLi is very easy. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using MySQLi for Selecting Limited Records



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Selecting limited data

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "LIMIT 3";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age columns of the top 3 records from Patient table.

AND/OR Operators In MySQL

In some of the previous articles, we studied how to filter data via, WHERE, IN and BETWEEN clauses. We can also set multiple conditions for filtering table data. For this purpose AND and OR operators are used MySQL. The AND operators works as logical AND and the OR operator works as the logical OR. The syntax for AND and OR operator is as follows:

SELECT column1, column2, column3 
FROM table
WHERE column1= value1 AND column2 IN (value1, value2, value3 ....)

Using AND/OR Operator in Object Oriented MySQLi

To join multiple WHERE clauses using AND/IN operator via MySQLi, pass the query for selecting filtered data to the query function of the mysqli object. The following example demonstrates this concept.




Using AND Operator in Object Oriented Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using AND Operator

$query = "SELECT patient_fname, patient_age ".
"FROM Patient ".
"WHERE patient_age BETWEEN 30 and 50 AND patient_fname LIKE '%x'";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age column of all the patients whose age is between 30 and 50 and whose patient_fname column has value that ends with ‘x’.

Using AND/OR Operator in procedural MySQLi

Selecting filtered table data via AND/OR operator in procedural MySQLi is very simple. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using AND Operator in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using AND Operator

$query = "SELECT patient_fname, patient_age ".
"FROM Patient ".
"WHERE patient_age BETWEEN 40 and 50 OR patient_fname LIKE '%n'";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age column of all the patients whose age is between 40 and 50 or whose patient_fname column has value that ends with ‘n’.

Using BETWEEN operator in MySQL

The last explained how we can select records of all the patients whose age is equal to some specific number. What if we want to get records of patients whose age is not equal to concrete value but it falls within a certain range. For instance, we might want to find records of all the patients between age 30 to 50. To do so, BETWEEN operator is used in MySQL.

SELECT column1, column2, column3 
FROM table
WHERE column1 BETWEEN value1 and value2

Using BETWEEN Operator in Object Oriented MySQLi

To select filtered data using BETWEEN operator via MySQLi, pass the query for selecting filtered data to the query function of the mysqli object. Have a look at the following example.




Using BETWEEN Operator in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using BETWEEN Operator

$query = "SELECT patient_fname, patient_age ".
"FROM Patient ".
"WHERE patient_age BETWEEN 40 and 50";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age column of all the patients whose age i is between 40 and 50 inclusive.

Using BETWEEN operator in Procedural MySQLi

Selecting filtered table data via BETWEEN operator in procedural MySQLi is straight forward. Just replace mysqli object with mysqli_connect function. Following example demonstrates this concept.




Using BETWEEN Operator in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using BETWEEN Operator

$query = "SELECT patient_fname, patient_age ".
"FROM Patient ".
"WHERE patient_age BETWEEN 50 and 80";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age column of all the patients whose age i is between 50 and 80 inclusive.

IN Operator in MySQL

Consider a scenario where you want to retrieve records of all the patients whose age is 30, 35, or 40. To do so, you can use three WHERE statements with OR operator between them. However there is another more concise and effective approach to achieve these results. You can use IN operator to select records of patients where patient_age is equal to certain values. The syntax of IN operator is as follows:

SELECT column1, column2, column3 
FROM table
WHERE column1 IN (value1, value2, value3 ....)

Using IN Operator in Object Oriented MySQLi

To select filtered data using IN operator via MySQLi, pass the query for selecting filtered data to the query function of the mysqli object. The following example demonstrates this concept.




Using IN Operator in Object Oriented Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using IN Operator

$query = "SELECT patient_fname, patient_age ".
"FROM Patient ".
"WHERE patient_age IN (30, 33, 40)";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age column of all the patients whose age is equal to 30, 33 or 40.

Using IN operator in Procedural MySQLi

Selecting filtered table data via IN operator in procedural MySQLi is very simple. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using IN Operator in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using IN Operator

$query = "SELECT patient_fname, patient_age ".
"FROM Patient ".
"WHERE patient_age IN (23, 35, 42)";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age column of all the patients whose age is equal to 23, 35 or 42.

Select Unique Data via Distinct In MySQL

Multiple records in MySQL table can have same value for different columns. For instance, many patients can have same first name and same age. What if you only want to select unique first names of the patient where patient’s first name doesnt report or you might want to see what are the different ages of the patient without repeating values for the age twice. The DISTINCT operator in MySQL allows to select unique column values. Syntax of DISTINCT operator is as follows:

SELECT DISTINCT column1, column2, column3 FROM table

Selecting unique MySQL table data via Object Oriented MySQLi

To select unique table data via MySQLi, pass the query for selecting unique data to the query function of the mysqli object. The following example demonstrates this concept.




Using MySQLi for Unique Data Selection



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// SELECT DISTINCT table data

$query = "SELECT DISTINCT patient_fname FROM Patient";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve unique values for patient_fname column of Patient table of the Hospital database.

Select Unique Table data via Procedural MySQLi

Selecting unique table data via procedural MySQLi is very simple. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using MySQLi for Unique Data Selection



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// SELECT DISTINCT table data

$query = "SELECT DISTINCT patient_age FROM Patient";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve unique values for patient_age column of Patient table of the Hospital database.

LIKE Query in MySQL

We know that WHERE clause can be used to filter data retrieved via SELECT statement. For instance you can select record of a patient whose first name is ‘abc’ via WHERE clause. However a problem with WHERE clause is that it looks for a perfect match. What if we want to retrieve record of all those patients whose first name starts with let’s say ‘b’ or the patient who have ‘er’ in their names? LIKE query allows us to select records that partially contain a specific string. Syntax of LIKE query is as follows:

SELECT column1, column2, column3 FROM table
WHERE column1 LIKE somestring AND/OR condition2 .... AND/OR conditionN

LIKE clause in Object Oriented MySQLi

To select filtered table data using LIKE query via MySQLi, pass the query for selecting data along with the condition in the LIKE clause to the query function of the mysqli object. Take a look at the following example.




Using LIKE clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing LIKE clause

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "WHERE patient_fname LIKE '%n'";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age columns for all records in Patient table where patient_fname column has a value that ends with ‘n’. Notice that ‘%’ symbol is used for specifying the position of the characters to be ignored. Here ‘%n’ there can be anything before ‘n’. Similarly ‘%er%’ means any string that contains ‘er’

LIKE clause in Procedural MySQLi

Selecting filtered table data using LIKE clause in MySQLi is very simple. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using LIKE clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing LIKE clause

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "WHERE patient_fname LIKE '%er%'";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above code will retrieve values for patient_fname and patient_age columns for all records in Patient table where patient_fname column has a value that contains “er” in it.

Sorting MySQL table data via PHP Script

In previous articles, we used select statement to fetch records from a database table. The data returned by select statement is in the order in which data exists in the table. However, you can sort the order of the selected data in MySQL. For instance, you select records from Patient table in the sorted order of age. To sort data in MySQL, the ORDER BY statement is used. The syntax of Ordery by query is as follows:

SELECT colum1, column2,...columnN FROM table1, table2...
ORDER BY colum1, colum2 ... ASC/DESC

Sorting MySQL table data via Object Oriented MySQLi

To sort table data via MySQLi, pass the query for sorting data to the query function of the mysqli object. Have a look at the following example.




Using ORDER BY clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing ORDER BY clause

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "ORDER BY patient_age";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above ORDER BY query will select the values for patient_age and patient_fname columns of the Patient table sorted by ascending order of age i.e the data of the patient with the smallest age shall appear on top.

Sorting MySQLi Table data via Procedural MySQLi

Sorting table rows via procedural MySQLi is straight forward. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using ORDER BY clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing ORDER BY clause

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "ORDER BY patient_age DESC";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The above ORDER BY query will select the values for patient_age and patient_fname columns of the Patient table sorted by descending order of age i.e the data of the patient with the largest value for age shall appear on top.

Deleting Data From MySQL Table via PHP Script

The last article focused on updating existing records in MySQL table. In this article, we shall study how to delete rows of data from MySQL table. For deleting data in MySQL, DELETE query is used. The syntax of DELETE query is as follows:

DELETE FROM table
WHERE condition(s)

Deleting records from MySQL table via Object Oriented MySQLi

To delete records from tables via MySQLi, pass the query for deleting rows to the query function of the mysqli object. Have a look at the following example.




Using DElETE clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing DELETE query

$query = "DELETE FROM Patient ".
    "WHERE patient_age < 30";
if ($connection->query($query) === TRUE) {
    echo "Record DELETED successfuly.";
} else {
    echo "Unable to DELETE record " . $connection->error;
}

$connection->close();
?>  
    



Download the code

The above DELETE query will delete all the records in the Patient table where age is less than 30. Now if you select and view the table contents, you shall see that there should be no patient with age less than 30.

Delete Table data via Procedural MySQLi

Deleting table data via procedural MySQLi is straight forward. Just replace mysqli object with mysqli_connect function. The following example demonstrates this concept.




Using DElETE clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing DELETE query

$query = "DELETE FROM Patient ".
    "WHERE patient_age = 27";
if ($connection->query($query) === TRUE) {
    echo "Record DELETED successfuly.";
} else {
    echo "Unable to DELETE record " . $connection->error;
}

$connection->close();
?>
        



Download the code

Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .