Databases
StackOverflow2013
Postsdbo
POMySQL query which has multiple subqueries, each with different join types
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL query which has multiple subqueries, each with different join types
    text
    copied!<p>Let me start by giving you a diagram of the relationships between the tables used in my mysql query:</p> <p><a href="http://eintranet.r717.net/index_htm_files/perm_relationships.png" rel="nofollow noreferrer">table relationship http://eintranet.r717.net/index_htm_files/perm_relationships.png</a></p> <p>I have a query that looks like this:</p> <pre><code>SELECT * FROM `permissions` WHERE `id` IN ( SELECT pr.perm_id FROM `user_roles` as ur LEFT JOIN `permissions_role` as pr ON ur.role_id = pr.role_id WHERE ur.user_id = '$userid' ) OR `id` IN ( SELECT `perm_id` FROM `permissions_user` WHERE `user_id` = '$userid' ) </code></pre> <p><code>$userid</code> is the id from the user table for the current user. I store the permission name from the result into an array which represents all permissions assigned to the user based on his/her role <strong>and</strong> his/her id:</p> <pre><code>&lt;?php $user_perms = array(); if(mysql_num_rows($query) &gt; 0): while($result = mysql_fetch_array($query): $user_perms[] = $result('name'); endwhile; endif; ?&gt; </code></pre> <p><code>print_r($user_perms);</code> produces an output that looks like:</p> <pre><code>Array ( [0] =&gt; ACCESS_TELEPHONELIST_PAGE [1] =&gt; ACCESS_VACATIONSCHED_PAGE [2] =&gt; ACCESS_TOURSCHED_PAGE [3] =&gt; ACCESS_WORKSCHED_PAGE [4] =&gt; ACCESS_RESOURCES_LINKS [5] =&gt; ACCESS_PMTOOL_PAGE [6] =&gt; ACCESS_TOOLSTOOL_PAGE [7] =&gt; ACCESS_SHOPTOOLLIST_PAGE [8] =&gt; ACCESS_TOOLINVENTORY_PAGE [9] =&gt; ACCESS_MANAGETOOLLIST_PAGE [10] =&gt; ACCESS_TOOLREPORTS_PAGE [11] =&gt; ACCESS_JOBSLIST_LINKS [12] =&gt; MAIN_TAB_TOOLSTOOL [13] =&gt; ADMIN_TAB_PODMANAGEMENT [14] =&gt; TOOL_TAB_SHOPTOOLLIST ) </code></pre> <p>What I want to do is to <strong>also</strong> store all of the user's roles into another array without making a second query. I thought I needed to use aliases for the sub queries, so I tried this query:</p> <pre><code>SELECT permissions.*, usersroles.role_id FROM `permissions` INNER JOIN ( SELECT ur.user_id, pr.perm_id, ur.role_id FROM `user_roles` as ur LEFT JOIN `permissions_role` as pr ON ur.role_id = pr.role_id WHERE ur.user_id = '$userid' ) AS usersroles ON usersroles.perm_id = permissions.id INNER JOIN ( SELECT `perm_id`, `user_id` FROM `permissions_user` WHERE `user_id` = '$userid' ) AS userperms ON userperms.user_id = usersroles.user_id AND userperms.perm_id = permissions.id </code></pre> <p>and, using code similar to the code above...</p> <pre><code>&lt;?php $user_perms = array(); $user_roles = array(); if(mysql_num_rows($query) &gt; 0): while($result = mysql_fetch_array($query): $user_perms = $result('name'); $user_roles = $result('role_id'); endwhile; endif; ?&gt; </code></pre> <p>... I get this warning:</p> <pre><code>Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given </code></pre> <p>But, I would like to <code>print_r($user_roles);</code> and produce an output that looks like this:</p> <pre><code>Array ( [0] =&gt; administrator [1] =&gt; humanresources [2] =&gt; podmanager ) </code></pre> <p>Can anyone tell me what I am doing wrong, or suggest a better way to get the data I need from <strong>one</strong> query into <strong>2</strong> arrays?</p> <p><strong>EDIT :</strong> After careful consideration, I changed my code to use 2 queries as ImreL suggested. The resulting code works nicely and executes quickly. I have edited my answer to show the final code I used and added supporting code to demonstrate how I am using the 2 queries. Big thanks to ImreL!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload