MySQL BETWEEN AND operator checks whether a value is within a range.
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise, it returns 0. This is equivalent to the expression (min
MySQL Version: 5.6
Example: MySQL BETWEEN . AND operator
The following MySQL statement will fetch the rows from the table publisher which was established between the year 1968 and 1975.
SELECT pub_name,country,pub_city,estd FROM publisher WHERE YEAR(estd) BETWEEN 1968 AND 1975;
Sample table: publisher
Sample Output:
mysql> SELECT pub_name,country,pub_city,estd -> FROM publisher -> WHERE YEAR(estd) BETWEEN 1968 AND 1975; +-------------------------+-----------+----------+------------+ | pub_name | country | pub_city | estd | +-------------------------+-----------+----------+------------+ | Jex Max Publication | USA | New York | 1969-12-25 | | New Harrold Publication | Australia | Adelaide | 1975-09-05 | | Mountain Publication | USA | Houstan | 1975-01-01 | +-------------------------+-----------+----------+------------+ 3 rows in set (0.01 sec)
Example: BETWEEN — AND operator with MONTH()
The following MySQL statement will fetch the rows from the table publisher which established between the month February and August.
SELECT pub_name,country,pub_city,estd FROM publisher WHERE MONTH(estd) BETWEEN '02' and '08';
Sample table: publisher
Sample Output:
mysql> SELECT pub_name,country,pub_city,estd -> FROM publisher -> WHERE MONTH(estd) BETWEEN '02' and '08'; +------------------------------+---------+-----------+------------+ | pub_name | country | pub_city | estd | +------------------------------+---------+-----------+------------+ | Ultra Press Inc. | UK | London | 1948-07-10 | | Pieterson Grp. of Publishers | UK | Cambridge | 1950-07-15 | +------------------------------+---------+-----------+------------+ 2 rows in set (0.00 sec)
Example: BETWEEN — AND operator using logical AND
The following MySQL statement will fetch the rows from the table publisher which established between the month May and September and year between 1950 and 1975.
SELECT pub_name,country,pub_city,estd FROM publisher WHERE MONTH(estd) BETWEEN '05' AND '09' AND YEAR(estd) BETWEEN 1950 AND 1975;
Sample table: publisher
Sample Output:
mysql> SELECT pub_name,country,pub_city,estd -> FROM publisher -> WHERE MONTH(estd) BETWEEN '05' AND '09' -> AND YEAR(estd) BETWEEN 1950 AND 1975; +------------------------------+-----------+-----------+------------+ | pub_name | country | pub_city | estd | +------------------------------+-----------+-----------+------------+ | New Harrold Publication | Australia | Adelaide | 1975-09-05 | | Pieterson Grp. of Publishers | UK | Cambridge | 1950-07-15 | +------------------------------+-----------+-----------+------------+ 2 rows in set (0.03 sec)
Example: BETWEEN — AND operator with a date range
The following MySQL statement will fetch the rows from the table publisher which estd between the specified dates.
SELECT pub_name,country,pub_city,estd FROM publisher WHERE estd BETWEEN '1950-01-01' AND '1975-12-31';
Relational Algebra Expression:
Relational Algebra Tree:
Sample table: publisher
Sample Output:
mysql> SELECT pub_name,country,pub_city,estd -> FROM publisher -> WHERE estd BETWEEN '1950-01-01' AND '1975-12-31'; +------------------------------+-----------+-----------+------------+ | pub_name | country | pub_city | estd | +------------------------------+-----------+-----------+------------+ | Jex Max Publication | USA | New York | 1969-12-25 | | New Harrold Publication | Australia | Adelaide | 1975-09-05 | | Mountain Publication | USA | Houstan | 1975-01-01 | | Pieterson Grp. of Publishers | UK | Cambridge | 1950-07-15 | +------------------------------+-----------+-----------+------------+ 4 rows in set (0.00 sec)
Publisher
Country
City
Date of establishment
query('SELECT pub_name,country,pub_city,estd FROM publisher WHERE estd BETWEEN "1950-01-01" AND "1975-12-31"') as $row) < echo "
"; echo "
" . $row['pub_name'] . "
"; echo "
" . $row['country'] . "
"; echo "
" . $row['pub_city'] . "
"; echo "
" . $row['estd'] . "
"; echo "
"; > ?>
Publisher
Country
City
Date of establishment
%> catch (Exception ex) < out.println("Can’t connect to database."); >%>
Online Practice Editor:
Slideshow of MySQL Comparison Function and Operators
Follow us on Facebook and Twitter for latest update.
Weekly Trends
Java Basic Programming Exercises
SQL Subqueries
Adventureworks Database Exercises
C# Sharp Basic Exercises
SQL COUNT() with distinct
JavaScript String Exercises
JavaScript HTML Form Validation
Java Collection Exercises
SQL COUNT() function
SQL Inner Join
JavaScript functions Exercises
Python Tutorial
Python Array Exercises
SQL Cross Join
C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook
In this article, we will 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.
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:
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
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
5. Close the connection object
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.
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"]. " "; > > else < echo "No Data"; >$connecting_data->close(); ?>
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.
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"]. " "; > > else < echo "No Data"; >$connecting_data->close(); ?>
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.
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.
Related Post
ABOUT THE AUTHOR
I specialize in creating and sharing insightful content encompassing various programming languages and technologies. My expertise extends to Python, PHP, Java, . For more detailed information, please check out the user profile
In this tutorial, we will create a Filter Range Of Date With MySQLi using PHP. This code can filter a range of table row from MySQLi when the user provides two dates from inputs. The code use MySQLi SELECT query to filter a variety of data in the MySQL row by providing two dates in the WHERE clause by adding a parameter BETWEEN . This a user-friendly program feel free to modify and use it to your system.
We will be using PHP as a scripting language and interpreter that is mainly used on any web server, including xamp, wamp, etc. It is being applied to any popular websites because of the modern approach as its today.
Getting Started:
First, you have to download & install XAMPP or any local server that run PHP scripts. Here’s the link for XAMPP server https://www.apachefriends.org/index.html .
And, this is the link for the bootstrap that I used for the layout design https://getbootstrap.com/ .
Creating Database
Open your database web server then create a database name in it db_range , after that click Import then locates the database file inside the folder of the application then click ok.
Creating the database connection
Open your any kind of text editor(notepad++, etc..). Then just copy/paste the code below then name it conn.php.
Creating The Interface
This is where we will create a simple form for our application. To create the forms simply copy and write it into your text editor, then save it as index.php.
PHP - Filter Range Of Date With MySQLi
Firstname
Lastname
Project
Date Submit
Creating the Main Function
This code contains the main function of the application. This code will filter a range of data when the button is clicked. To do that just copy and write this block of codes inside the text editor, then save it as range.php.
0) < while($fetch=mysqli_fetch_array($query))< ?>
>else < echo'
Record Not Found
'; > >else < $query=mysqli_query($conn, "SELECT * FROM `member`") or die(mysqli_error()); while($fetch=mysqli_fetch_array($query))< ?>
> ?>
There you have it we successfully created Filter Range Of Date With MySQLi using PHP. I hope that this simple tutorial help you to what you are looking for. For more updates and tutorials just kindly visit this site. Enjoy Coding!
