Selecting rows from a table based on results from other tables in Mysql and PDO

Question

Hi,

I'm kind of stumped.

I have a table of items with fields: ID, title, created_by, group.

I also have another table of groups with fields: ID, created_by, group_name
and a final table of users with fields: ID, username, password etc.

The idea is that only items created by the logged in user or items where the logged in user is part of the item's user group can be seen by that user.

Each user can be part of an unspecified number of groups and each group can contain an unspecified number of users.

The only way at the moment that I can think of doing this is to have a separate table for each user (listing all the groups of which they are members).

I then first search the items table for items created by the logged in user.
Secondly, search that user's "groups" table to find the ids of each group to which they belong and then recursively search through the items table again and load each item where the currently found group_id matches the item's group id.

I know this will work if coded correctly for small numbers of items, groups and/or users but I suspect each page would take a long while to load/process for larger tables. It also seems quite messy to have a new table for each user given that you could have thousands of users and therefore thousands of tables.

I suspect that table joins may provide the solution but I don't really see how at the moment. If this is the case I'm perfectly happy to rename the table fields to achieve this.

My current code for retrieving the items is this (I know it's probably not ideal):

$query = "SELECT * FROM items WHERE user_id=:u_id";
$stmt = $conn->prepare($query);
$stmt->execute(array(':u_id'=>$_SESSION['u_id']));
$exist = '<option></option>';
while( $uRow = $stmt->fetch() ) {
    $exist .= '<option value="'.$uRow['id'].'">'.$uRow['title'].'</option>';
}
$user_groups_tbl = "user_groups_".$_SESSION['u_id'];
$query1 = "SELECT * FROM $user_groups_tbl";
$query2 = "SELECT * FROM items WHERE group_id=:group";
$stmt1 = $conn->prepare($query1);
$stmt2 = $conn->prepare($query2);
$stmt1->execute();
while( $gRow = $stmt1->fetch() ) {      
    $stmt2->execute(array(':group'=>$gRow['group_id']));
    while( $row = $stmt2->fetch() ) {
        if( $row['user_id'] !== $_SESSION['u_id'] ) {
            $exist .= '<option value="'.$uRow['id'].'">'.$uRow['title'].'</option>';
        }
    }
}
 return $exist;  

I hope my needs and intentions are clear. Any help would be appreciated.

Answer 1

The only way at the moment that I can think of doing this is to have a separate table for each user (listing all the groups of which they are members).

Yikes! Don't do that! Let's recreate your tables using a bit of normalization.

-- Our users
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    ...
);

-- And our groups
CREATE TABLE groups (
    group_id INTEGER PRIMARY KEY,
    ...
);

-- New!  A list of all groups and the users that belong to them.
-- This is also conveniently a list of users and the groups that they belong to.
CREATE TABLE group_users (
    user_id INTEGER REFERENCES users(user_id),
    group_id INTEGER REFERENCES groups(group_id),
    UNIQUE KEY(user_id, group_id)
);

-- Finally, our mysterious "items"
CREATE TABLE items (
    item_id ...,
    title ...,
    user_id INTEGER REFERENCES users(user_id),
    group_id INTEGER REFERENCES groups(group_id)
);

Given:

The idea is that only items created by the logged in user or items where the logged in user is part of the item's user group can be seen by that user.

SELECT *
  FROM items
 WHERE items.user_id = ?
    OR items.group_id IN(
           SELECT group_id
             FROM group_users
            WHERE user_id = ?
       )

This should grab all items that were created by the user and also all items that are part of a group that the user belongs to. (Note: This query may not be optimized properly depending on your MySQL version. You may need to convert the subquery in the WHERE clause to be in the FROM clause instead.)

Answer 2

The relationship (I think) you're looking for is called has-and-belongs-to-many or HABTM. You will need what's called a pivot table with the feilds user_id and group_id.

Then if you've got a user you can find all the group_id's that the user_id is associated with. Then you can use that to find the items.

Answer 3

Hey drent, that's great question. The standard way to deal with a many-to-many relationship (many users to many groups) is by using a join table, as you mentioned. Here are the tables:

• items: ID, title, created_by, group_ID
• groups: ID, created_by, group_name
• users: ID, username, password, etc.
• and just one new table - users_groups: user_ID, group_ID

Now, whenever a user joins a group, just add a row to the user_group table. For example, if user 327 joins group 14:

INSERT INTO users_groups SET user_ID=327, group_ID=14;

To list all the items that a user can access:

SELECT * FROM items
JOIN users_groups ON users_groups.group_ID = items.group_ID
WHERE users_groups.user_ID = :user

Make sure to replace :user with the user ID of the current user. Also, I may not be using the exact same column names as you have in your tables, so take care.

To list the groups that a user is a member of:

SELECT * FROM groups
JOIN users_groups ON users_groups.group_ID = groups.ID
WHERE users_groups.user_ID = :user