How To Use SQL BETWEEN Operator With Example In PHP

Posted By Bajarangi soft , Posted On 18-09-2020

The SQL BETWEEN operator used to test if an expression is within a range of values.BETWEEN operator is inclusive. The values can be used in a SELECT, INSERT, UPDATE, or DELETE SQL statement. The SQL BETWEEN operator will return the records within the range of value1 and value2.

SQL BETWEEN Operator
Selects values(numbers, text, or dates) within a given range.

Syntax for SQL BETWEEN Operator

SELECT column_name list
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Let's Learn.
1.Create database and table
Student table
for this student table we need to use SQL BETWEEN Operator.

StudentID	StudentName	Address	DOB	Section	Branch	Percentage	Fee
1	Anikth	23 Andrew road	1995-04-18	A	CSE	66	66000
2	Andrew	2415 Anthony market str	1996-08-20	A	ISE	80	65000
4	Bhavya	18 Christopher sq.	1996-07-13	B	EE	67	70000
5	Charles	878 Eli Berguvsvägen 8	1995-05-20	C	ECE	52	75000
8	Rajesh	23 Andrew road	1995-02-03	A	CL	65	66000
9	Daniel	2415 Anthony market str	1995-09-10	A	ME	80	65000
10	Jackson	18 Christopher sq.	1995-10-17	B	EEE	63	70000
11	Connor	878 Eli Berguvsvägen 8	1995-11-11	C	ECE	52	75000
12	Kiran	banglore	1996-12-26	C	ME	68	68500

Example (1)

SELECT * FROM Student WHERE Percentage BETWEEN 60 AND 70;

Selecting Percentage between 60 to 70 that students percentage will be listed.

Example (2)

SELECT * FROM Student WHERE Percentage NOT BETWEEN 60 AND 70

Shows all students Percentage list of students but NOT between 60 to 70

Example (3)

SELECT * FROM Student WHERE Percentage BETWEEN 60 AND 70 AND StudentID NOT IN (1,2,3)

Selecting Percentage between 60 to 70 that students percentage will be listed except StudentID 1,2,3

Example (4)

SELECT * FROM Student WHERE StudentName BETWEEN 'Charles' AND 'Kiran' ORDER BY StudentName"

Selecting Student Name between charles AND kiran that students names will be listed.

Example (5)

SELECT * FROM Student WHERE StudentName NOT BETWEEN 'Ankith' AND 'Jackson' ORDER BY StudentName

Shows all students Name lists but NOT between Anikith AND Jackson

Example (6)

SELECT * FROM Student WHERE DOB BETWEEN '1995-11-23' AND '1997-01-20'

selecting students DOB between the range of 1995-11-23 AND 1997-01-20

2.Complete code of SQL BETWEEN Operator in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>How to use SQL BETWEEN Operator</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="text-center">
        <h1>SQL BETWEEN Operator</h1>
    </div>
    <div class="well">
        <?php
        $connect = mysqli_connect("localhost", "root", "", "school_management");

        if ($connect->connect_error) {
            die("Connection failed: " . $connect->connect_error);
        }
        $sql_1 = "SELECT * FROM Student WHERE Percentage BETWEEN 60 AND 70";
        $sql_2= "SELECT * FROM Student WHERE StudentName BETWEEN 'Charles' AND 'Kiran' ORDER BY StudentName;";
        $sql_3= "SELECT * FROM Student WHERE DOB BETWEEN '1995-11-23' AND '1996-01-20'";
        //  $sql_1= "SELECT * FROM Student WHERE Percentage NOT BETWEEN 60 AND 70";
        //$sql_2= "SELECT * FROM Student WHERE Percentage BETWEEN 60 AND 70 AND StudentID NOT IN (1,2,3);";
        // $sql_3= "SELECT * FROM Student WHERE StudentName NOT BETWEEN 'Ankith' AND 'Jackson' ORDER BY StudentName";

        echo "<h2 class='text-danger'>SQL BETWEEN Operator by Numeric value</h2>";
        if($result_1 = mysqli_query($connect, $sql_1)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                    <th>StudentID</th>
                    <th>StudentName</th>
                    <th>Address</th>
                   <th>DOB</th>
                    <th>Section</th>
                    <th>Branch</th>
                    <th>Percentage</th>
                    <th>Fee</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                  <td>" . $row['StudentID'] . "</td>
                  <td>".$row['StudentName'] ."</td>
                
                  <td>".$row['Address'] ."</td>
              
                  <td>".$row['DOB'] ."</td>
                  <td>".$row['Section'] ."</td>
                  <td>".$row['Branch'] ."</td>
                  <td>".$row['Percentage'] ."</td>
                  <td>".$row['Fee'] ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }

        echo"<br>";
        echo "<h2 class='text-danger'>SQL BETWEEN Operator by Text value</h2>";
        if($result_2 = mysqli_query($connect, $sql_2)){
            if(mysqli_num_rows($result_2) > 0){
                echo "<table class='table'>
                    <tr>
                    <th>StudentID</th>
                    <th>StudentName</th>
                    <th>Address</th>
                   <th>DOB</th>
                    <th>Section</th>
                    <th>Branch</th>
                    <th>Percentage</th>
                    <th>Fee</th>
                    </tr>
                    ";
                while($row = $result_2->fetch_assoc()){
                    echo "<tr>
                  <td>" . $row['StudentID'] . "</td>
                  <td>".$row['StudentName'] ."</td>
                
                  <td>".$row['Address'] ."</td>
              
                  <td>".$row['DOB'] ."</td>
                  <td>".$row['Section'] ."</td>
                  <td>".$row['Branch'] ."</td>
                  <td>".$row['Percentage'] ."</td>
                  <td>".$row['Fee'] ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }


        echo"<br>";
        echo "<h2 class='text-danger'>SQL BETWEEN Operator by Date value</h2>";
        if($result_3 = mysqli_query($connect, $sql_3)){
            if(mysqli_num_rows($result_3) > 0){
                echo "<table class='table'>
                    <tr>
                    <th>StudentID</th>
                    <th>StudentName</th>
                    <th>Address</th>
                    <th>DOB</th>
                    <th>Section</th>
                    <th>Branch</th>
                    <th>Fee</th>
                    </tr>
                    ";
                while($row = $result_3->fetch_assoc()){
                    echo "<tr>
                  <td>" . $row['StudentID'] . "</td>
                  <td>".$row['StudentName'] ."</td>
                
                  <td>".$row['Address'] ."</td>
                 
                  <td>".$row['DOB'] ."</td>
                  <td>".$row['Section'] ."</td>
                  <td>".$row['Branch'] ."</td>
                  <td>".$row['Fee'] ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }

        ?>
    </div>
    <br>
</div>
</body>
</html>