conan conan - 4 months ago 8
MySQL Question

trying to create a query when greater and less than inside the mysql table php

Look at my table below. When I have a value and cat, I want to check the id. If I have cat

a
and value
3
, then I will get id
1
, but if I get cat
a
and value
1
, then return false, because value
1
is not greater than
2
. The problem is that the greater sign is inside the table. I cannot figure a way to do it on mysql query, help please. I can alter the table value structure if need.

table
id cat value
1 a greater than 2
2 b less than 2
3 c less than 2
4 d greater than 5

$val = 3;
SELECT id FROM my_table WHERE value=$val and cat = a

Answer
table 
id     cat       lbound       ubound
1       a         2            2147483647  (Assuming signed int)
2       b         -2147483648  2
3       c         -2147483648  2
4       d         5            2147483647

$cat = 'a';
$val = 3;
SELECT id 
FROM my_table 
WHERE cat = $cat
   AND lbound <= $val -- If you use nulls instead of default extreme values,
   AND ubound >= $val -- then these two bounds comparisons will be more complicated
ORDER BY lbound DESC LIMIT 1 -- if overlapping ranges are possible

Design-wise I would prefer nulls for default "no bound" values, but expressions using OR's like AND (lbound IS NULL OR lbound <= $val), or functions like IFNULL(lbound,-2147483648) <= $val tend to disqualify index use.