help with a mysql_query

[mufka]

I'm using PHP but I think this question falls under MySQL.

I am querying a mysql database and pretty much putting all of the fields into an array. I need to narrow down the contents of the array by doing a calculation with two of the fields. For example field1 = 20 and field2 = 50. My input value is 30 and so since it falls between the value of field1 and field2, I want it to stay in the array. Anything that falls outside of field1 and field2, I want to delete. Is there a way to do this?

 

[Kozusnik]

SELECT * FROM table WHERE $input BETWEEN field1 and field2;

Is that what you're looking for? If so, no array manipulation is necessary.

Mark

 

[mufka]

Thanks, I'll try that. One twist though: Will it work if either field1 or field2 are equal to $input? I'll play with it to see if it works but if it doesn't I'll need an alternative.

 

[mufka]

This method has some potential. One problem is that if either of the fields in the database is blank, that record comes up regardless of what $input is (and that's bad). Works ok if one of the fields are equal to $input.

 

[Kozusnik]

SELECT * FROM table
WHERE field1 != '' AND field2 != ''
AND $input BETWEEN field1 and field2

Although I wouldn't let an integer field be null (blank). I'd probably default it to 0.

Mark

 

[mufka]

I don't think that would work. The problem is that one of the fields could be null(0) and I still need to account for the other field and compare it to $input. If one field is null the other field has to equal $input for it to evaluate true.

Assume that I've taken your advice and changed all the null fields to 0.
With the following factors:
field1 = 0
field2 = 50
$input = 30
It would evaluate to true because 30 is between 0 and 50. And that would be wrong.

With the following factors:
field1 = 0
field2 = 50
$input = 50
It would evaluate to true. And that would be correct.

 

[guelphdad]

Although I wouldn't let an integer field be null (blank). I'd probably default it to 0.

except NULL and blank are not the same thing at all.

question if you set a numeric field to default as 0 what do you do to differentiate between that and an actual mark of 0 for instance? with NULLS you can get a proper count of your field, without them you can't.

suppose you have a table with a list of grades students achieved on a test.

Billy wrote the test and somehow submitted an empty quiz and got a 0. Gerrald was ill that day and didn't write the test but is going to be allowed to make up the test results. His mark isn't a 0 before the rewrite, it is NULL because you don't know what result he will end up with.