So I have a fairly long complex query but the gist is that the query's basic functionality looks like this SELECT verification_id FROM verification_table
The verification_id returns an integer from 0-3. Is there a way to do something in the SQL query where if the verification_id is 0, it returns a string like "new" and different ones for all 4 verification_id's.
I can do this in the backend via PHP but i was wondering if there was a way to do it via MySQL
SELECT CASE
WHEN verification_id = 0 THEN 'new'
WHEN verification_id = 1 THEN 'something else'
ELSE 'error?'
END AS myvalue
FROM verification_table
something like this should work.
Altough you may consider using the mysql ENUM field type which basically translates text strings into numbers and vice versa. It is an efficient field type of storing such values.
You want a case statement. It can be used for boolean conditionals as well as for selecting between multiple outcomes...
SELECT [Condition]
CASE WHEN TRUE THEN 'True'
ELSE 'False'
END
FROM [Table]
Note that the return type of each branch must be the same - The column can only be of one type