Processing of data output from PHP database

Question

In many cases we have data in the database that is not in the format we want them to be shown.

As an example, let’s say I have a table called users and in this table a field called role which will store a user’s access level. Let’s say the value of role be it ADMIN.

When I plan to output this data after retrieving it from the database, I don’t want the user to see the value of the field role (which in this case is ADMIN) but I want him to see DIRECTOR. This is a fictitious example to illustrate that actually there are many situations where in the database the stored data is one but the output of it to the user must be another.

My question is: how do you usually treat it at the level of PHP?

Using the framework Cakephp i am currently using the callback afterFind which takes the current result obtained in the database and creates an additional element in the array with the index the and there I treat all the output data.

An example. Suppose I got the following array of results from the database:

user_id => 1,
username => 'joao',
role => 'ADMIN'

After passing through my callback, the array will be:

user_id => 1,
username => 'joao',
role => 'ADMIN'
o =>
    user_id => 1,
    username => 'joao',
    role => 'DIRETOR'

And at the time of output, that is to print in the view, I only use the index the. The rest are for internal use in controllers.

Answer 1

You can do this in both PHP and SQL, but I usually store the user scroll data in a table of roles.

In SQL, just create two aliases and take one formatted and one unformatted for the name:

SELECT u.user_id, u.username, IF(u.role='ADMIN','DIRETOR',u.role) AS role_with_director, u.role as role_global from users u
Inner join roles r ON(u.id_fk_role=r.id_role);

You can also use CASE:

 SELECT u.user_id, u.username,
 CASE u.role
    WHEN 'ADMIN' THEN 'DIRETOR' 
    WHEN 'SALLER' THEN 'VENDEDOR'  
    WHEN 'RESALLER' THEN 'REVENDA' 
 ELSE u.role
 END as role_formated,
   u.role as role_global from users u
   Inner join roles r ON(u.id_fk_role=r.id_role);

In PHP I would create a method within the class to handle the output:

private function setNameRole($role) {

 $nameRole = '';
   switch($role) {
      case 'ADMIN':    $nameRole = 'DIRETOR'; break;
      case 'SALLER':   $nameRole = 'VENDEDOR'; break;
      case 'RESALLER': $nameRole = 'REVENDA'; break;
  } 
  return $nameRole;
}

Or in array:

 private function setNameRole($role) {

     $roleNames = array(
                    'ADMIN'    => 'DIRETOR',
                    'SALLER'   => 'VENDEDOR',
                    'RESSALER' => 'REVENDA');

     return $roleNames[$role];
 }

Answer 2

I imagine/suggest you have a 'roles' table where you will determine the permissions of each access level. In this table you must have at least one column 'role' and another 'friendlyName'.

+-------+--------------+
| role  | friendlyName |
+-------+--------------+
| ADMIN | DIRETOR      |
+-------+--------------+

And to make your query you would use the clause:

SELECT user_id, username, role, (SELECT friendlyName FROM roles WHERE roles.role = users.role) as friendlyRoleName FROM users;

The result must be something like this:

+---------+----------+-------+------------------+
| user_id | username | role  | friendlyRoleName |
+---------+----------+-------+------------------+
|       1 | joao     | ADMIN | DIRETOR          |
+---------+----------+-------+------------------+

For greater stability of your code, don’t forget to define key relationships between tables.