Hacking SQL: when you dont know if you have a search criteria

So I was wondering.... sometimes you dont know if you need to define your search a little more by adding another column and another variable.
What do I mean? I mean this:

select * from table where something = 1
so the 'something = 1' part defines the search.

BUT, what if you sometimes need to use 'something = 1' and sometimes you would just like it to search everything?


Doing it in the application layer/logic

Well, basically, what I used to do is play around with it in my application. For example:

$sqlstr = 'select * from table';
if ($something > 0 ){
$sqlstr = ' where something = 1';
}

As you can see, I first made the generic search. In case that I need to define it further, I simply add to the existing string with the search criteria.
But I always wondered, how can you just do it directly in the SQL statement..


Defining the SQL directly

So I tried to do something using the IF() function.

select * from table where something = if($myvar = null, all, $myvar)

This doesn't work, but I just wanted to show you what I was going for.
(you can use ifnull(), in fact its better if you do).
Then I improved it to this:

select * from table where something like if($myvar = null, '%', $myvar)

Now here, you have something that really works. If you have something, it uses it. If not, it uses a wild card that searches everything.
However, the downside of this is that % is its mainly used for strings and it forces the engine of the database to search through every single row. (you can find this out using EXPLAIN before your statement)
So what I finally decided to go for, is the rather long BETWEEN which helps more with numbers.

select * from table where something between if($myvar = null, 0, $myvar) and if($myvar = null, 99999999, $ myvar)

Ok, so this is very ugly. For the simple reason that the '9999999' is an assumption and what I know from coding methods, is that assuming is something that will make your code possibly not work in the future. For example, one day you may go beyond this number. Even if you choose the highest number of the column, for example SMALLINT UNSIGNED is 0 to 65,535, who says one day they might not decide to extend it to int or long?

The Solution

So I was looking at someone else's website and I found the answer:

SELECT PatientNumber, LastName, FirstName, etc FROM Patient WHERE
(PatientNumber = @PatientNumber OR @PatientNumber IS NULL)
AND (@LastName IS NULL OR LastName LIKE @LastName+'%')
AND (@FirstName IS NULL OR FirstName LIKE @FirstName+'%')
AND (DOB = @DOB OR @DOB IS NULL)
AND (Phone = @Phone OR @Phone IS NULL)

Well, that 'IS NULL OR' trick just got me. If your variable is null, then it technically disregards everything in brackets. Its really incredibly handy and I'm very jealous I didn't think of it first >.<

Conclusion

What I was going for is creating stored procedures, where you have all the possible search criteria's, for example:

call mysearch(something, startdate, enddate, price,whatever)

and even if you didn't fill all of them out, it would still work with just 1 complicated line of SQL.


Until next time, thanks for reading my blog.