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(); ?>
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(); ?>
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.