Interesting ideas. The LIKE clause idea could work, but I think it will hurt performance. Appending another where clause would require string manipulation of the query which I wanted to avoid. If I wanted to go down that route, then I could just add the 'item = ?' clause directly to the query string...
–
user27478Aug 7 '10 at 14:41

2

Extending second example: SELECT * FROM table WHERE item = ? OR 1=? Put 0 in the second parameter if you want specific rows, or 1 if you want all
–
MchlAug 7 '10 at 14:43

I think that MySQL will short circuit a LIKE '%' statement into 1 = 1... Try it (and I doubt it'll hurt performance much even if it's not a short circuit, since you'd need a full table scan anyway)...
–
ircmaxellAug 7 '10 at 14:43

@Mchl that was what I had in my last revision (Now restored). I was worried about performance, but on second thought, the query optimizer will probably take care of it.
–
Pekka 웃Aug 7 '10 at 15:09

1

Im pretty sure optimiser will deal with it great. AFAIR x OR 1 is always optimised to 1 and x OR 0 is optimised to x.
–
MchlAug 7 '10 at 15:13

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.

The actually query I'm running is much more complicated than the example I gave. Following the DRY principle, I don't want to have to maintain two copies of what is essentially the same query (plus or minus one where clause).
–
user27478Aug 7 '10 at 14:36

1

If I understand you correctly, you can still construct your query in one place, branching only on whether your $item parameter has a value. Wouldn't consider that a DRY violation.
–
mwittrockAug 7 '10 at 14:41

@gerdemb, if one query has a WHERE condition, and another doesn't, by no stretch of the imagination can they be considered 'essentially the same query'. It is a big mistake to assume that because the text of two different queries is similar that the semantics of the two queries are similar.
–
Brian HooperAug 7 '10 at 16:37

@Brian Perhaps I over simplified my example. My actually query is over ten lines long including two sub-queries and has a series of WHERE clauses. The only difference is the addition of one more clause to filter by item. For my purposes both queries are very similar.
–
user27478Aug 7 '10 at 17:00

@gerdemb, if that's so, you are quite right. I hope I was not discourteous. This is a pet peeve of mine, having worked in a management context that thought that if you only 'had to change a couple of lines' that the result was the same and it would only take 'a few minutes'.
–
Brian HooperAug 7 '10 at 17:32