Map integer(id) to text string?

Question

In my database, I'm storing a field called "type" as tinyInt, since I don't have that many types I decided not to create a table for storing the corresponding names of all these types.

When I query my table I want the types to be replaced by their corresponding names which I'm storing in php arrays. I wonder if there is a way to do this replacement inside the sql statement itself instead of looping through the results and do replacement on the returned result row.

Does something like this even exists:

select *, map(type, {1=>'abc', 2 => 'xyz'}) from orders

Answer 1

The only way (I'm fairly sure) to do this is using a CASE .. WHEN ... construct:

SELECT *, CASE type WHEN 1 THEN 'abc' WHEN 2 THEN 'xyz' END as stringType
FROM orders

You can also use the ELSE clause to specify a default, e.g. CASE type WHEN 1 THEN 'abc' WHEN 2 THEN 'xyz' ELSE 'unknown' END.

Answer 2

Actually, in contrast to the answer by mathematical.coffee, there actually is another way in MySQL to map a number to text - at least if your numbers are contiguous:

The ELT() function returns the N-th element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments.

Example:

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'

Source:

https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_elt