Morpheus Morpheus - 28 days ago 8
SQL Question

SQL: show 20m less and 20m more of searched term

I have the following:

$p['int_length_min'] = 'products.length';
$p['int_length_max'] = 'products.length';

if (!empty($params['int_length_min']) && !empty($params['int_length_max'])) {
$query .= db_quote(' AND int_length > ( ?i - 20 ) AND int_length <= ?i ', $params['int_length_min'], $params['int_length_max']);
}


Not sure how to convert this to following: instead of having 2 fields, I would like to have 1, and when a user enters some number, I would like to get back - / + 20, for example if user enter 100, I would like to return results from 80 - 120. I tried adding (?i + 20) in that statement but I never got anything back, like below:

$p['int_length_m'] = 'products.length';

if (!empty($params['int_length_m'])) {
$query .= db_quote(' AND int_length > ( ?i - 20 ) AND int_length <= ( ?i + 20) ', $params['int_length_m']);
}


Any ideas?

Thanks.

Answer

To have only one parameter that filters on a range, use a yoda between:

...
AND ?i BETWEEN int_length - 20 AND int_length + 20
...

or in code:

$query .= db_quote(' AND ?i BETWEEN int_length - 20 AND int_length + 20', $params['int_length_m']);