MySQL where clause equals anything (SELECT * WHERE col = ANY_VALUE)

Question

I'd like to create a query in MySQL that has an optional value. When the value is specified the query is filtered by that value, when the value is not all rows are returned. Here's the idea:

public function doQuery($item = 'ANY_VALUE') {
  $query = "SELECT * FROM table WHERE item = ?";
  db->fetchAll($query,array($item))
  ...
}

doQuery(); // Returns everything
doQuery($item='item1'); // Returns only rows where item = 'item1'

Is there an easy way to do this without creating two query strings depending on the value of $item?

Answer 1

As far as I know, no such "any" placeholder exists.

If you can use LIKE, you could do

SELECT * FROM table WHERE item LIKE '%'

if you can append a condition, you could nullify the item clause like this:

SELECT * FROM table WHERE item = ? OR 1=1

(won't work in your example though, because you are passing "item" as a parameter)

That's all the options I can see - it's probably easiest to work with two queries, removing the WHERE clause altogether in the second one.

This would probably work, but I*m not sure whether it's a good idea from a database point of view.

public function doQuery($item = 'ANY_VALUE') {
  $query = "SELECT * FROM table WHERE item = ? OR 1 = ?";
  db->fetchAll($query,array($item, ($item == 'ANY_VALUE' ? 1 : 0))
  ...
}

Answer 2

There's really no reason to do this with one query. Running the query with no WHERE clause makes it explicit that you want to return all rows. Running it with a WHERE clause makes it explicit that you want to filter the rows.