CODEWITHSUNDEEP
phpmysql
StartedFromTheBottom
StartedFromTheBottom

Reputation: 325

MySQL statement if number is less than php variable

Is there any possibility to make an MySQL statement to select the number if it is less than some value? Lets say I have a php variable: myvar = 123;

And I have some records in database: 120 125 129 121

I want to take all possible database records, where myvar and record difference is less than 5. In this example, it would take 120, 125 and 121. Is it possible to do this? If yes, maybe someone could help me out how?

Upvotes: 1

Views: 511

Answers (3)

Mark Miller
Mark Miller

Reputation: 7447

You can use mysql's BETWEEN operator:

$query = "
SELECT * FROM `table` 
WHERE `record` BETWEEN {$myvar}-5 AND {$myvar}+5";

See mysql demo

Note: BETWEEN will match records between min and max, including min and max. Keep this in mind when developing the logic for your application.

Upvotes: 3

Ipsita Rout
Ipsita Rout

Reputation: 5179

SELECT * FROM `table_name` 
WHERE `field_name` BETWEEN (123-4) AND (123+4)


In generalized form as below.
$myvar = 123;
$difference = 5;

$sql = "SELECT * FROM `table_name` 
        WHERE `field_name` BETWEEN ($myvar - $difference +1) AND ($myvar + $difference -1)";

Explanation: ->As mentioned difference should less than 5 .NOT LESS THAN OR EQUAL TO 5.

Lets' Say the required nos are x.

123-x < 5 => 118 < x => 119,120,121,122,123 x-123 > 5 => x > 128 => 127,126,125,124,123

Hence x could be 119 to 127.

=>(123-5)+1 < x < (123+5)-1

Hope this explanation clarifies the sql i wrote above.

Comment if it doesn't solve your problem.

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

SELECT *
FROM `table_name`
WHERE `field` <= your_variable+5
  AND `field` >= your_variable-5

Check SQL Demo

Upvotes: 1

Related Questions