Articles

PHP MySQL - BETWEEN operator

PHP MySQL - BETWEEN operator


Let’s discuss 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 with 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 in 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 refers 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 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 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 column 2 and 8.

 

Example 2-

Return the records from the Treatment_Doctor and Doctor_Experience columns from 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 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 by the end of this article, we saw how to perform PHP-MySQL query with BETWEEN operator inside 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.


PHP MySql

Would you like to see your article here on tutorialsinhand. Join Write4Us program by tutorialsinhand.com

About the Author
Gottumukkala Sravan Kumar 171FA07058
B.Tech (Hon's) - IT from Vignan's University. Published 1400+ Technical Articles on Python, R, Swift, Java, C#, LISP, PHP - MySQL and Machine Learning
Page Views :    Published Date : Jun 30,2023  
Please Share this page

Related Articles

Like every other website we use cookies. By using our site you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service. Learn more Got it!