mysql - search in database table by multiple column that maybe not set

Question

i have a database table with any column. and there is 3 form input in UI page, that a user can search a value in table by these inputs. the user can insert value for one of column (inputs), Two of columns or all of them. means its possible that one or two of inputs value be null and user don't use them for his search. how to create the search query? because the following code have a problem. if user don't insert the value of all of 3 column, this query will take error.

$query="SELECT * FROM insurance WHERE nationalCode=:nationalCode AND insuranceNumber=:insuranceNumber 
AND insuranceType=:insuranceType;";

in other words, i need to search in table across 3 columns if value of 3 these columns have define by user else if user have insert value for 2 of columns search will do by these two columns & if user have insert value only for 1 of column search will do by that 1 column.

Answer 1

If the user did not insert a value, say for nationalCode, I assume the you pass NULL (or an empty string?) as :nationalCode and in this case your condition should be:

nationalCode = :nationalCode OR :nationalCode IS NULL

or:

nationalCode = :nationalCode OR :nationalCode = ''

So for all the columns the statement should be:

SELECT * 
FROM insurance 
WHERE (nationalCode = :nationalCode OR :nationalCode IS NULL)  
  AND (insuranceNumber = :insuranceNumber OR :insuranceNumber IS NULL)   
  AND (insuranceType = :insuranceType OR :insuranceType IS NULL);