CODEWITHSUNDEEP
mysql
Bob
Bob

Reputation: 177

Getting mysql to ignore where condition

Is there to make mysql ignore conditions if a variable isn't set, e.g.

SELECT *
FROM foo
WHERE id = $id
AND bar = $baz

If $baz is set run query as normal, else run the query minus the AND clause?

Thanks

Upvotes: 4

Views: 4187

Answers (5)

user1322977
user1322977

Reputation: 151

Thanks to Tim Medora. Your solution worked. I was not clear at first, but when I used it worked. So here is the story If you use following, it is acts like skiping the AND section when $y is Null.

AND ( $y IS NULL OR y = $y )

If $y is null, then it means it is 

AND( TRUE or y=$y)

which means 

AND TRUE

So this means the AND section has no effect on rest of WHERE clause. In other word, it is skipped.

Upvotes: 1

Tim M.
Tim M.

Reputation: 54368

SELECT *
FROM foo
WHERE id = $id
AND ( bar = $baz OR $baz [equals null, empty string, zero, whatever] )

I don't know if MySQL ever uses short circuit evaluation in its execution plan, but it may be beneficial to put the cheaper comparison first, like:

SELECT *
FROM foo
WHERE id = $id
AND ( $baz [equals null, empty string, zero, whatever] OR bar = $baz )

You can use this methodology with multiple parameters.

SELECT *
FROM foo
WHERE id = $id
AND ( $baz [equals null, empty string, zero, whatever] OR bar = $baz )
AND ( $x = 0 or x = $x )
AND ( $y IS NULL OR y = $y )

-- etc.

Upvotes: 7

Nicolas78
Nicolas78

Reputation: 5144

$q = "SELECT ... id=$id "
if ($baz)
  $q .= " AND bar = $baz";

I assume these are PHP variables?

Upvotes: 2

Bohemian
Bohemian

Reputation: 424993

Yes. You add a condition that is actually a condition on your variable, like this:

SELECT *
FROM foo
WHERE $baz is not null -- this condition tests if $baz is set
AND (
    ... -- all "normal" conditions, including any involving $baz
)

If $baz is not set, all other conditions are bypassed

Upvotes: 0

Spencer Ruport
Spencer Ruport

Reputation: 35107

Just make a variable $ignoreWhere and write it as:

SELECT * FROM foo WHERE $ignoreWhere = 1 OR id = $id

Set it to 1 to ignore the rest of the statement.

Upvotes: 1

Related Questions