Database Relationships, I can't understand, MYSQL, PHP

Question

I'm pretty new on using multiple tables in mysql, so i really don't understand some things..

Here is my php code from my role.model.php.

public function getRolePerms($role_id) {

    $str = "SELECT t2.perm_desc FROM role_perm as t1
    JOIN permissions as t2 ON t1.perm_id = t2.perm_id
    WHERE t1.role_id = :role_id";

    $sth = $this->db->prepare($str);
    $sth->execute(array(':role_id' => $role_id));

    $data = $sth->fetchAll(PDO::FETCH_ASSOC);
    print_r($data);
}

I called it in my role.php controller and fed a dummy $role_id of 1; getRolePerms(1);

I print_r($data) so i will know whether it will fetch a data but the output is just

"array()"

My question is, how does this works?

If i have these tables:

roles
permissions
role_perms

with role_id and perm_id columns, referencing the primary key of roles and permissions.

Kinda confused. Can someone really explain to me, in a simple language?

Answer 1

Did you check the role_perms table whether there is a row with a role_id of 1? Because there should be for the query to work.

how does this work?

First, try understanding the tables and their relationships. I would imagine your tables are somewhat like the following tables.

The ROLE table contains roles in your application (e.g. Admin, Guest).

Table 1: ROLE
-----------------------------------------------
  role_id        role_description
-----------------------------------------------
     1               Admin
     2               Guest

The PERMISSIONS table contains permissions in your application.

Table 2: PERMISSIONS
-----------------------------------------------
  permission_id        permission_description
-----------------------------------------------
     1                 Can create files
     2                 Can edit files
     3                 Can view files

The ROLE_PERMISSIONS table contains the permissions of the different roles in your application. Take the data below for instance, it means role 1 (Admin) has permissions 1, 2, and 3 (can create, edit, view files respectively).

Table 3: ROLE_PERMISSIONS
-----------------------------------------------
  role_id        permission_id
-----------------------------------------------
     1                1
     1                2
     1                3
     2                3

Now, try understanding the query.

SELECT t2.permission_description
FROM role_permissions as t1 JOIN permissions as t2
ON t1.permission_id = t2.permission_id
WHERE t1.role_id = 1

The first line instructs what data should be retrieved. It says to select the values from table t2 in the column permission_description. Therefore, possible results of the query are:

Can create files
Can edit files
Can view files

The second & third lines instructs from where data should be retrieved. It says to retrieve data from the joint table of role_permissions and permissions ON the condition:

t1.permission_id = t2.permission_id

Note: AS indicates that an alias will be used. Meaning, t1 is just the same as role_permissions.

From the PERMISSIONS and ROLE_PERMISSIONS tables above, the joint table would look like below.

Table 4: Joint ROLE_PERMISSIONS and PERMISSIONS
------------------------------------------------------------------------
  role_id    permission_id    permission_id    permission_description
------------------------------------------------------------------------
     1             1               1              Can create files
     1             2               2              Can edit files
     1             3               3              Can view files
     2             3               3              Can view files

This is the table where data will be retrieved.

So far, the instruction is to retrieve the permission_description from the joint table (Table 4).

The fourth line adds a constraint to the first instruction to only select permission_descriptions WHERE role_id = 1. Therefore, the complete instruction is

retrieve permission_descriptions from the joint table (Table 4) where the role_id is 1.

Which in plain english means:

Retrieve the permission description of the role 'Admin'.

Thus the result of the query is:

Table 5: PERMISSION_DESCRIPTIONS of Admin
-----------------------------------------------
  permission_description
-----------------------------------------------
     Can create files
     Can edit files
     Can view files

Hope this helps you!

Answer 2

SELECT t2.perm_desc FROM role_perm as t1
JOIN permissions as t2 ON t1.perm_id = t2.perm_id
WHERE t1.role_id = :role_id;

this command tells MySQL to join role_perm and permissions by they're shared columns (perm_id), this is correct. but then we're missing the connectivity between the role ids in these 2 tables. the join results in one table containing the columns in the permissions table where the perm_id exists in the role_perm table as well, regardless of whether role_perm.role_id is pointing at the right role_id. the where clause filters only t1. this might result in getting permissions that other roles have as well. to solve this, change the SQL command to

SELECT t2.perm_desc FROM role_perm as t1
JOIN permissions as t2 ON t1.perm_id = t2.perm_id AND t1.role_id=t2.role_id
WHERE t1.role_id = :role_id;