Page 1 of 1

Include null values in querry

Posted: Fri Dec 15, 2006 8:18 am
by sarris
Hi there,
Is there any way to include the null values in a querry?
What i am doing exactly is that i have a form from where i give some minimum and maximum values for some fields and pass it to a PHP script to execute a querry. In the querry i use the
where field1 between field1_min and field2_min and all is allright.
What i want to do though, is to be able to give ALL the results, including the records with null value in that field, when the user selects as minimum and as maximum "Any". I want to do without making the code full of IF's and many different cases.

My code looks smth like that

WHERE price between $pricemin and $pricemax AND
size between $sizemin and $sizemax OR AND
extras_num between $extrassmin and $extrassmax

Any ideas?

Posted: Fri Dec 15, 2006 8:47 am
by feyd
NULL won't help here. Switching the logic used will however. If "any" is selected for the minimum, for example, adjust the query such that you request for values below the maximum -- no between clause. Work it similarly to maximum. When both minimum and maximum are "any" then you can pull the constraint out for that field.

Posted: Fri Dec 15, 2006 9:35 am
by sarris
Cool...I'll do that. What was my problem was that i though i would have to have many different sql querries for every combination of values...What i hadnt thought was that i can create the querry string step by step at the moment i am getting the values from the form.
Thanks

PS: its amazing how fast you reply...thanks a lot
Question: Isnt there an option here to rate the answer or characterize the thread as resolved??

Posted: Fri Dec 15, 2006 4:52 pm
by feyd
sarris wrote:PS: its amazing how fast you reply...thanks a lot
You're most welcome.
sarris wrote:Question: Isnt there an option here to rate the answer or characterize the thread as resolved??
Throw money. :lol:

The only thing we have so far is people marking their post as solved by editing the thread title to add "[SOLVED]" to the beginning.