The other day I hit a simple MySQL syntax situation I hadn’t run across in a while and the MySQL docs were lacking.

I like to push as much result analysis and alteration back to MySQL as possible. I was taught that it was faster and that seems to be true (although I have never seen a side-by-side comparison against doing result manipulation with PHP at the Apache level. [Newbie Notice!] if you find yourself using a lot of string manipulation functions, look into alternatives. Your code is probably doing more work than needed.)

I had a query that required that I grab a version level of a document via a subselect. The twist was I had to describe any revision level older than a specific version with the remark “un-tested”, else show the revision level. Seemed straightforward at the time but for some reason my subselect kept returning null. I was using the conditional CASE operator with syntax I had learned quite a while ago. I assumed I had bad syntax so I went to look it up at the MySQL 5.0 Control Flow Functions documentation page. I found:

CASE WHEN [condition] THEN result [WHEN [condition]
THEN result …] [ELSE result] END;

Fine, but all the further examples assumed that I had a static alternative value for ELSE.

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

But for anyone like me who wants to confirm the syntax for returning the DB value for ELSE instead, here you go.

SELECT CASE WHEN version < 5 THEN 'un-tested' ELSE version
END FROM product WHERE id=x;

BTW, don’t Google for “mysql case” you’ll get “MySQL :: MySQL 5.0 Reference Manual :: 21.2.10.2 CASE Statement” as the first result. That’s not what you want. That syntax is for Stored Procedures.