Handling data output from the database in PHP

Question

Handling data output from the database in PHP

Navigation

#1 by (1 votes)
#2 by (1 votes)

4

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

As an example, let's say I have a table called users and in this table a field called role that will store a user's access level. Let's say the role value is ADMIN .

When I want to output these data after retrieving them from the database, I do not want the user to see the value of the role field (which in this case is ADMIN ) but I want him to see DIRECTOR . This is a fictitious example to illustrate that there are actually 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 this at the level of PHP ?

Using the framework CakePHP I'm currently using callback afterFind that takes the current result from the database and creates an extra element in the array with the o index and 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 going 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, ie print in the view, I only use the or index. The rest are for internal use in the controllers.

php cakephp

asked by anonymous 20.02.2015 / 17:40

2 answers

Insert "Watermark" on video at upload Recursive Query

score 1 · Answer 1

I imagine / suggest you have a 'roles' table where you will determine the permissions for each level of access. 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 should look something like this:

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

For greater stability of your code, do not forget to define the relationships between table keys.

score 1 · Answer 2

You can do this in both PHP and SQL, but I usually store the user's role data in a role table.

In SQL, just create two aliases and get 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 treat 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];
 }