CODEWITHSUNDEEP
phpmysqlbetween
Akshay Vaghasiya
Akshay Vaghasiya

Reputation: 1657

How to use exclusive BETWEEN clause in mysql?

I have a php variable. I want check either it lies between values of two columns of any row of mysql table or not. But I do not want match that variable with exact values of table. i.e, exclusive BETWEEN clause. If anyone know answer then please explain with an example. Thank You. Here is my code.

$var = '15:00:00';


//Mysql Table (datatype of columns are TIME)

          first         last
row1      13:00:00      15:00:00
row2      17:00:00      19:00:00

Upvotes: 4

Views: 17718

Answers (3)

spencer7593
spencer7593

Reputation: 108500

There is no "exclusive BETWEEN" operator in MySQL. The expression:

  a BETWEEN b AND c

is shorthand equivalent to:

  ( a >= b AND a <= c )

We can use different comparison operators if we like:

  ( a > b AND a < c )

If there's a requirement to include BETWEEN to achieve a an equivalent, then we could do

  ( a BETWEEN b AND c AND a NOT IN (b,c) )

but I think the SQL is easier if we avoid using BETWEEN.

To check whether there are any rows that match a value, we can supply a second copy of the same value into a second condition e.g.

  SELECT 1 AS `matched`
    FROM mytable t
   WHERE t.first < ?
     AND t.last  > ? 
   LIMIT 1

Upvotes: 13

MaxusR
MaxusR

Reputation: 61

Actually, you can use expressions in your query.

SELECT *
FROM `table_name`
where $var between `first` + INTERVAL 1 second AND `last` - INTERVAL 1 second

You can choose any intervals you need. And this even works with prepared statements.

Upvotes: 2

Meenesh Jain
Meenesh Jain

Reputation: 2528

You can do it with between clause 

  SELECT `column1`,`column1`,`column1` 
   FROM table_name 
   WHERE $var > `first` AND $var < `second`

something like this will only work if the value is in middle of something from first and second

Upvotes: 0

Related Questions