How do I change mysql output based on value

Question

For example, if queue_watch shows a 0, I want the actual mysql output to say No, else say Yes. Changing the scheme is not an option.

SELECT user.user_name, queue.queue_name, queue_access.queue_access,
    queue_access.queue_watch
FROM queue_access
INNER JOIN user ON user.user_id = queue_access.user_id
INNER JOIN queue ON queue.queue_id = queue_access.queue_id
WHERE queue_access <> '' OR queue_watch = 1
ORDER BY user_name;

The SQL works for what I need, but I like to know if changing the output using SQL rather than PHP is possible. Also, is it possible to group all the queue_names in one row to each user_name so I don't have to have 153 rows returned.

Thanks

Answer 1

It not an over head. It is better to be done in the query, I feel.

Try this -

SELECT user.user_name, queue.queue_name, queue_access.queue_access,
       IF(queue_access.queue_watch = 0, 'No', 'Yes')
FROM queue_access
INNER JOIN user ON user.user_id = queue_access.user_id
INNER JOIN queue ON queue.queue_id = queue_access.queue_id
WHERE queue_access <> '' OR queue_watch = 1
ORDER BY user_name;

Answer 2

You can use the standard CASE expression. There are two slightly different ways to write a CASE expression.

Simple case expression:

SELECT
    CASE queue_access.queue_watch
        WHEN 0 THEN 'No'
        ELSE 'Yes'
    END AS your_alias,
    ...

Searched case expression:

SELECT
    CASE WHEN queue_access.queue_watch = 0 THEN 'No' ELSE 'Yes' END AS your_alias,
    ...

Or you could use the MySQL specific IF construct:

SELECT
    IF(queue_access.queue_watch, 'Yes', 'No') AS your_alias,
    ...

You may want to consider what should happen if your value is something other than 0 or 1.