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.

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.

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.