CODEWITHSUNDEEP
mysqlselectmultiple-conditions
user2867504
user2867504

Reputation: 95

Select multiple conditions where one can be null

I'm passing several parameters to mysql query. Some of them can be "all" or "null". How can I use one query for that?

How can I pass, for example, action=ALL, user=3, what=ALL. By ALL I mean all action possible options.

SELECT id, user, action, what, from_value, to_value, updated_date 
from history 
where (user=? and action=? and what=?) 
order by updated_date DESC

Upvotes: 1

Views: 2567

Answers (1)

Dave
Dave

Reputation: 327

You can use WHERE field_name IS NULL or WHERE field_name IS NOT NULL to specifically filter on whether or not something is NULL. If it doesn't matter what the value of a field is, you don't need to include it in your WHERE clause.

In your example, you're building a query based on POST data. If you want to filter on the value of $h_action, but it's okay if $h_action is null, you can do:

WHERE (action = ? OR action IS NULL)

And you can chain those together with ANDs for multiple fields:

WHERE (action = ? OR action IS NULL) AND (what = ? OR what IS NULL) AND ... etc.

Upvotes: 1

Related Questions