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:

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.

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?

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.