title: Nested SELECT in mysqli with prepared statements
body: <p>Im trying to list out a table to show user permission in a PHP-application.
  Im trying to do it with MySQLi, but need some help on the way.</p>
  
  <p>I have theese tables:</p>
  
  <pre><code>tblUsers(userID PK, username)
  tblUsersRoles (roleID PK, roleName)
  tblUsersPermission (permissionID PK, permissionName)
  tblUsersUserRole (userID FK, roleID FK)
  tblUsersRolesPermissions (roleID FK, permissionID FK)
  </code></pre>
  
  <p>I want to show a table like this for each user:</p>
  
  <pre><code>-------------------------------------
  - RoleName     - PermissionName     -
  -------------------------------------
  - Admin        - Edit profile       -
  -              - Edit permissions   -
  -              - Create invoice     -
  -------------------------------------
  - UserAdmin    - Add user           -
  -              - Edit user          -
  -              - Delete user        -
  -------------------------------------
  </code></pre>
  
  <p>How is this possible? I have tried with nested prepared statements, but only get errors that I dont know enogh to fix myself. Can anybody help?</p>
  
  <p>After what I have read it has somthing to do about store_result(), but I cant seem to get hold of it...</p>
  
  <p>My code so far is this:</p>
  
  <pre><code>    &lt;table class="table table-striped"&gt;
      &lt;tr&gt;
          &lt;th&gt;RoleName&lt;/th&gt;
          &lt;th&gt;PermissionName&lt;/th&gt;
      &lt;/tr&gt;
      &lt;?php
          //Get roles where the user is a member from tblUsersUserRoles
          $userID = $_SESSION['userID'];
          $table = DBPREFIX.'tblUsersUserRoles UR, '.DBPREFIX.'tblUsersRoles R';
          $query = "SELECT UR.roleID, R.roleName FROM $table WHERE R.roleID = UR.roleID AND UR.userID=? ORDER BY UR.roleID";
          $statement = $mysqli-&gt;prepare($query);
          $statement-&gt;bind_param('i', $userID);
          $statement-&gt;execute();
          $statement-&gt;bind_result($roleID, $roleName);
  
          while($statement-&gt;fetch()) {
              echo '&lt;tr&gt;';
              echo '&lt;td&gt;'.$roleName.'&lt;/td&gt;';
              echo '&lt;td&gt;&lt;/td&gt;';
              echo '&lt;/tr&gt;';
  
  
              //Get permissions for this role
              $table2 = DBPREFIX.'tblUsersPermissions P, '.DBPREFIX.'tblUsersRolesPermissions RP';
              $query2 = "SELECT P.permissionName FROM $table2 WHERE P.permissionID = RP.permissionID AND RP.roleID=? ORDER BY P.permissionGroup";
              $statement2 = $mysqli-&gt;prepare($query2);
              $statement2-&gt;bind_param('i', $roleID);
              $statement2-&gt;execute();
              $statement2-&gt;bind_result($permissionName);
  
              while($statement2-&gt;fetch()) {
                  echo '&lt;tr&gt;';
                  echo '&lt;td&gt;&lt;/td&gt;';
                  echo '&lt;td&gt;'.$permissionName.'&lt;/td&gt;';
                  echo '&lt;/tr&gt;';
              }
              $statement2-&gt;close();
          }   
          $statement-&gt;close();
  
      ?&gt;
  &lt;/table&gt;
  </code></pre>