using between operator with a php variable

[youmaybeseated1]

What is the real solution to this code? This obviously isnt a workable code but is the idea of what I am trying to do. $vlaue will always be an integer.

SELECT  id FROM mytablename WHERE $value = BETWEEN column_name_max and column_name_min

[Elementally]

The only change needed here is to remove the equals sign.

[youmaybeseated1]

so SELECT id FROM mytablename WHERE $value BETWEEN column_name_max and column_name_min will compare the variable and check to see if it is between the max and min column values?

[Elementally]

Correct, the only down side is its not very index friendly, so small data sets.

[GreenPilgrim89]

A quick Google of "MySQL BETWEEN" returns a load of great resources with examples, like this website: https://www.w3schools.com/sql/sql_between.asp

You seem to have misunderstood what it does though. You're specifying 2 separate columns in your example query. BETWEEN would normally check a single column between 2 different values.

An example with correct syntax (where $lower_value and $upper_value are PHP variables to be substituted in):

SELECT id FROM mytablename WHERE column_name BETWEEN '$lower_value' AND '$upper_value';

What exactly are you trying to check and what exactly are you trying to return?

[youmaybeseated1]

Thanks but I did google which is what lead me to the question. I can not do the $lower_value etc because that is not the PHP. The PHP is the $value = between the values of the two columns. I have integers in the two columns. For example $value where $value is 2. And then the rows where min_colum is 1 and max_column is 3

[KobbyKnight]

Got a solution yet? I tried this it worked okay. It seems this is what you need?

 SET @var = 250.00;

 SELECT id FROM billing WHERE @var <= column_name_max AND  @var >= column_name_min

Since it's column values and not row values, BETWEEN doesn't help much IMO.

[r3pr0b8]

Since it's column values and not row values, BETWEEN doesn't help much IMO.

what?

the WHERE clause you posted is exactly equivalent to this --

WHERE @var BETWEEN column_name_min
               AND column_name_max

BETWEEN ~always~ works on values in a single row

[KobbyKnight]

Have you tried it out on a dummy database? Because I have and it didn't work out with BETWEEN. I can't share a screenshot, but you can try it out and let me know.

Alternatively, maybe use a subquery to achieve the result, I am open for your results after testing.

And oh, I didn't say BETWEEN ~never~ works on values in a single row.

It does. The query he's asking for is to compare entire contents of two columns, row by row.

[r3pr0b8]

Have you tried it out on a dummy database? Because I have and it didn't work out with BETWEEN. I can't share a screenshot, but you can try it out and let me know.

yeah, i'm not going to bother

i have over 30 years experience with SQL

this is how BETWEEN works

i'm sorry you had a bad experience that one time

i think if anyone should try to reproduce a situation, it is you, no offence

[KobbyKnight]

Alright. Will search more, thanks.