[SOLVED] question regarding BETWEEN Syntax

[abdfahim]

the following code

SELECT * FROM `table1 WHERE `col1` BETWEEN a AND b 

will work only if a>b. But I don't know whether a or b is greater, but I always want to return the values between a and b. what should i do?

 

[Cazrin]

I guess you could put an if() statement before that, to check which is greater.

 

<?php
$a = 400;
$b = 300

if($a>$b) {

// Swap the values of $a and $b
$temp = $b;
$b=$a; // Is now 400
$a=$temp; // Is now 300

}

$query = "SELECT * FROM `table1` WHERE `col1` BETWEEN ".$a." and ".$b;
?>

 

Haven't used BETWEEN before so not sure if that would work   But I'd say give it a go, makes sense to me!

[abdfahim]

yup, that's a nice solution. But isn't there any built in function in SQL which will solve this?? may be not !!

[fenway]

What do you mean by "you don't know"... is this value in the db or not?

[abdfahim]

no, both a and b is the input given by the user in two text boxes. I distinguish the two text boxes as higher and lower, but if some one just give inverse entry, then it will show error.

[fenway]

no, both a and b is the input given by the user in two text boxes. I distinguish the two text boxes as higher and lower, but if some one just give inverse entry, then it will show error.

You could get mysql to determine this for you, but that's much more work when any type of validation would prevent this issue on the server side.