In this article, we will see how to include the BETWEEN operator in WHERE Clause with the SQL SELECT Query through PHP to connect with the MySQL database in the XAMPP Server.

Consider the table - treatments present in the database - hospital snapshot.

Schema:

Here, there are 4 columns with the following data types:

1. Treatment_ID with int type of length - 2
2. Treatment_Name with varchar type of length - 100
3. Treatment_Doctor with varchar type of length - 100
4. Doctor_Experience with int type of length - 2.

Records:

There are 6 records in the treatments table.

Now we will see about the BETWEEN operator.

BETWEEN is MySQL is used to filter the records by returning values in the given range from a particular column. It is used with WHERE Clause.

WHERE in MySQL is used with SELECT which is used to filter the record inside a table by specifying a condition. Here the condition is specified by using the BETWEEN Operator.

SQL BETWEEN- Syntax

SELECT columns… from TABLE_NAME WHERE column BETWEEN first_value AND second_value;

As we know that columns refer to the column names that exist in the given table (TABLE_NAME).

first_value and second_value represent the range such that we will return the records from a particular column based on the values in the range.

PHP MySQL - BETWEEN Object-oriented Connection

We will see how to connect PHP with a MySQL database in XAMPP Server using PHP Script using Object oriented Format to implement the  BETWEEN operator.

Approach

1. Specify the credentials to create a connection object.

The credentials include the XAMPP Server localhost name, username, password, and database name.

$connecting_data =  new mysqli($server, $user, $pwd, $data);

2. Specify the SQL Query that includes the BETWEEN operator.

SELECT columns… from TABLE_NAME WHERE column BETWEEN first_value AND second_value;

3. Get the query into the connection object.

$return = $connecting_data->query($my_query);

4. Return the records from the result query.

if ($return->num_rows > 0) {
  while($record_data = $return->fetch_assoc()) {
   // Display the data
  }
} else {
  echo "No Data";
}

5. Close the connection object

$connecting_data->close();

Example 1

Return the records from the Treatment_Doctor and Doctor_Experience columns from the treatments table where values in the Doctor_Experience column in between 2 and 8.

<?php
// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Mention the SQL Query to return the Treatment_Doctor, Doctor_Experience records with Doctor_Experience between 2 and 8
$my_query = "SELECT Treatment_Doctor, Doctor_Experience FROM treatments WHERE Doctor_Experience BETWEEN 2 and 8";

$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
  while($record_data = $return->fetch_assoc()) {
    echo "Doctor Name: " . $record_data["Treatment_Doctor"]. " and Doctor Experience: " . $record_data["Doctor_Experience"]. "<br>";
  }
} else {
  echo "No Data";
}
$connecting_data->close();
?>

Output:

So we specified first_value as 2 and second_value as 8. Hence the records are in between with Doctor_Experience columns 2 and 8.

Example 2

Return the records from the Treatment_Doctor and Doctor_Experience columns from the treatments table where values in the Doctor_Experience column in between 100 and 200.

<?php
// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Mention the SQL Query to return the Treatment_Doctor, Doctor_Experience records with Doctor_Experience between 100 and 200
$my_query = "SELECT Treatment_Doctor, Doctor_Experience FROM treatments WHERE Doctor_Experience BETWEEN 100 and 200";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
  while($record_data = $return->fetch_assoc()) {
    echo "Doctor Name: " . $record_data["Treatment_Doctor"]. " and Doctor Experience: " . $record_data["Doctor_Experience"]. "<br>";
  }
} else {
  echo "No Data";
}
$connecting_data->close();
?>

Output:

There is no value in the given range. So No data is returned.

Example 3

Return the records from the Treatment_ID and Treatment_Name columns from the treatments table where values in the Treatment_ID column in between 1 and 3.

<?php
// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Mention the SQL Query to return the Treatment_ID, Treatment_Name records with Treatment_ID between 1 and 3.
$my_query = "SELECT Treatment_ID, Treatment_Name FROM treatments WHERE Treatment_ID BETWEEN 1 and 3";

$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {

  while($record_data = $return->fetch_assoc()) {

    echo "Treatment ID: " . $record_data["Treatment_ID"]. " and Treatment Name: " . $record_data["Treatment_Name"]. "<br>";
  }

} else {

  echo "No Data";
}
$connecting_data->close();
?>

Output:

There are four records in the given range.

Conclusion

So the end of this article, we saw how to perform a PHP-MySQL query with BETWEEN operator inside the WHERE clause in XAMPP Server by running PHP Script. We discussed three different examples to filter the records from the table by providing the range of values within WHERE Clause using the BETWEEN operator. First, we have to create a connection to the XAMPP by specifying the correct credentials. Then we can write SQL Query and fetch the filtered data and finally, we have to close the connection using the connection object.