CODEWITHSUNDEEP
mysqlsqldatabasecodeigniter
Firdaus Indradhirmaya
Firdaus Indradhirmaya

Reputation: 314

How to change query result into another value in MYSQL?

For example, I have a column named 'form_type' consisted of int (1-4)

id_event|form_type
--------|----------
001     |1
001     |3
001     |4
002     |2
002     |1

When i query

SELECT form_type FROM some_table WHERE id_event = 001

I would get 1,3 and 4 of course. But is it possible to change those values to something else on the database layer? For example, I would get strings instead of int like so, 1 = 'Name', 3 = 'Email', 4 = 'City' etc. If it's possible, what would be the query? Or is the only way to do that would be on the application side?

Upvotes: 1

Views: 1099

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The normal approach would be to have a reference table in the database with this information. Then you would use JOIN:

create table forms (
    form_type int primary key,
    name varchar(255)
);

insert into forms (form_type, name)
    values (1, 'Name'),
           (2, ???),  -- unclear what goes here
           (3, 'Email'), 
           (4,  'City');

Then use JOIN to get the name:

select t.*, f.name
from t join
     forms f
     using (form_type);

Why do you want to store this information in the database? Simple: CONSISTENCY!

If you start using CASE expressions in your queries, you are proliferating the problem. This is risky:

  • Different case expressions might use different strings (say, "E-mail" versus "email").
  • If you add new forms, you have to change a lot of code.
  • If you decide to change the name of something then you have to change a lot of code.

You want the data stored in tables. You do not want this implemented as business rules in the code, because that is brittle and error-prone.

I should note that MySQL does offer two alternatives to an explicit reference table. If form_type is only used in one table, then an enum is a convenient alternative. Or, you can use a generated column in the table to generate the string.

Upvotes: 1

Akina
Akina

Reputation: 42728

SELECT ELT(form_type, 'Name', 'Unknown', 'Email', 'City')
FROM some_table
WHERE id_event = 001

ELT() function

But I'd recommend to create a table which stores the relation between the number and the string, and join it into the query for to get strings instead of int.

Upvotes: 1

Gharbad The Weak
Gharbad The Weak

Reputation: 1641

Yes, you can do something like this:

SELECT
    CASE
        WHEN form_type = 1 THEN 'Name'
        WHEN form_type = 3 THEN 'Email'
        WHEN form_type = 4 THEN 'City'
        ELSE 'some default value'
    END
FROM some_table
WHERE id_event = 001

Using CASE you define what you want returned for the value in your table. CASE example here.

Hope that helps!

Upvotes: 2

Related Questions