SQL query

Use whereBetween

$record = Model::whereBetween('111', ['column_1', 'column_2'])->get():

https://laravel.com/docs/5.6/queries

chain two where statements

$record = table1::where('column_1','>=',$number)
                  ->where('column_2','<',$number)
                  ->firstOrFail();

chained, they are AND ed together

Tray in your method I get error "Column not found: 1054 Unknown column '111'".

Snapey in your method I get no record back and there is one.

Any other ideas?

Show some actual data. What I showed should work just fine if your understanding of the data is correct

I just copied you code and I don't know what '111' is.

Are you trying to check that column_1 is bigger than 111 and column_2 is less than 111?

SELECT * 
FROM table1
WHERE column_1 > 111
AND column_2 < 111;

That is exactly the code @Snapey wrote for you in Eloquent but with hardcoded 111.

What does the data look like and what is the desired result?

@tray2 whereBetween only checks one column between two values, not the other way around.

Yes it should.

SELECT * 
FROM table1
WHERE column_1 >= 111
AND column_2 <= 111;

Does not work,

As I said I have it working in Sequel Pro with this query.

SELECT * FROM table_1 WHERE '111' BETWEEN column_1 AND column_2;

I have tried a bunch and get an error or no record.

So your row contains something like

column_1 = 150
column_2 = 25

yet the first example does not work. I cannot think why not.

@Snapey Good to know mate. Never used it myself :)

@ChrisPercival your SQL looks so wonky to me and I never seen anything like it and I work with SQL every day. Give us some data and what you expect to get out of the query.

Ok if

column_1 = 100
column_2 = 200

then in Sequel Pro this works

SELECT * FROM table_1 WHERE '111' BETWEEN column_1 AND column_2;

and this does not.

SELECT * 
FROM table1
WHERE column_1 >= 111
AND column_2 <= 111;

Ok I ran your query and

SELECT * 
FROM table1
WHERE column_1 >= 111
AND column_2 <= 111;

Does not give the correct result but

SELECT * 
FROM table1
WHERE column_1 <= 111
AND column_2 >= 111;

Does.

So

$record = table1::where('column_1', <=',$number)
                  ->where('column_2',' >=',$number)
                  ->firstOrFail();

Should work.

Just change the <>

because your comparators are the wrong way around.

SELECT * 
FROM table1
WHERE column_1 <= 111
AND column_2 >= 111;

but think hard about what you want to happen when either column_1 or column_2 are exactly 111

He used <= >= but in code it becomes

<=

>=

For some reason

In testing, I noticed that

SELECT * FROM orders WHERE 111 BETWEEN column_1 AND column_2;

only works if column_1 is the lower bound and column_2 is the upper bound. It does not work if the values are reversed meaning that it does not actually seem to check if the number is BETWEEN, its actually checking if value is more than the first clause and less than the second.

Like I said I never seen SQL written in that way before and I read and write alot of it every day. Even if it kinda works it just feels off to me.

Thanks guys

I did try it. There must have been a mistake in my code.

So this works

$record = table1::where('column_1', <=',$number)
                  ->where('column_2',' >=',$number)
                  ->firstOrFail();

The query in your very first post would have worked if column_1 was the upper number and column_2 the lower, but without values or a hint what column_1 and column_2 actually were then we could not get to the right answer straight away.

This is why I always prefer real code than abstracted answers

For instance if the query was

function aliveIn($year)
{
    return People::where('birthdate','>=',$year)->where('died','<=',$year)->get();
}

then the intent of the code would have been clearer