Mysql function to return text according to numeric index

Question

I have a table called tb_usuarios with the columns

id_usuario
nome
email
telefone
tipo_de_acesso

I would like to create a function in Mysql, where when using echo $row['tipo_de_acesso'], already come the correct type, but in text mode, and not numerical, among those I delimit:

Tipo = 1: Administrador
Tipo = 2: Usuário
Tipo = 3: Cliente

How can I create a function within Mysql to automate this, rather than return 1, 2 or 3, then return Administrador, Usuário or Cliente?

Answer 1

Two solutions with Mysql:

Function ELT()

SELECT ELT( tipo, 'administrador', 'usuario', 'cliente');

ELT returns the string of the position indicated by the first parameter, in the case of the column corresponding to the tipo.

The advantages of ELT are the fact that it does not need to touch the table structure, and can decide its use when designing the SELECT.

The downside is that if you use the column in one SELECT * (which is no longer the most recommended in all situations) will return numerically, forcing its literal use throughout SELECT who needs the textual information.

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

Column ENUM

Perhaps simpler is to convert the column to

 ENUM ( 'Administrador', 'Usuário', 'Cliente' )

See the ENUM working on SQL Fiddle.

The advantages are that it returns in text both in a SELECT * how much SELECT tipo, and still allow the numerical return with SELECT 0+tipo. Its internal storage is numerical, spending the same as the smallest integer needed for the number of options. Also, it works with WHERE tipo = 2 and also with WHERE tipo = 'Usuário', and in the same way you can do the INSERT text or full text, both working normally.

Also, you can convert a numerical column to ENUM without losing existing numerical data, it is only important to take care to make the conversion with the items in the correct order.

The main drawback is that when adding, removing or editing any type, you need to tinker with the table structure, and it requires extra care when reordering the types so as not to change the logical order in order to "shuffle" the data already entered. Also, depending on how much you want to respect the application layers, "embedding" this type of information into the table structure may hurt some principles, but then it’s a matter of you understanding the pros and cons in your specific case.

Handbook: https://dev.mysql.com/doc/refman/5.7/en/enum.html

If it were in PHP

This is completely different from what you asked, but I’ll just leave an example as an alternative for any visitors. A simple PHP equivalent (based on your example in the question) would be this:

Before an exhibition loop, something like this:

$tipos_de_acesso=array('', 'administrador', 'usuario', 'cliente');

(the column '' is for the zero index, as the 'administrador' is a)

And in the loop, instead of echo $row['tipo_de_acesso'];, use this:

echo $tipos_de_acesso[$row['tipo_de_acesso']];

Obviously you should take some care to avoid higher indices than the size of the array, but it was only to illustrate one of the simplest ways.