How to display specific rows/columns in a many-to-many PHP/SQL relationship?

Question

I'm using a many-to-many relationship (using three tables - users, groups, relations) to connect users within a group. I am trying to find a way to display the users registered in a group. Here is my code so far:

Search for a group by name through POST form:

    $errors = array();

    if(isset($_POST['groupSearchSubmit'])){
        $groupSearchName = trim($_POST['groupSearchName']);
        $query = "SELECT * FROM groups WHERE groupName = '$groupSearchName'";
        $result = mysqli_query($link, $query);

        if(mysqli_num_rows($result) != 1){
            $errors['groupSearch'] = 'No group was found with that group'
                    . 'name. Enter a different group name and try again.';
        }

        if(!$errors){
            if(mysqli_num_rows($result) == 1){
                $searchedGroupRow = mysqli_fetch_array($result, MYSQLI_BOTH);
                $_SESSION['searchedGroupID'] = $searchedGroupRow['groupID'];
                $_SESSION['searchedGroupName'] = $searchedGroupRow['groupName'];

            }
            header('Location: searchedGroup.php');
        }
    }

Work in progress to display users in the searched group:

    $searchedGroupID = $_SESSION['searchedGroupID'];
    $query = "SELECT * FROM relations WHERE groupID = '$searchedGroupID'";
    $result = mysqli_query($link, $query);

    while($row = mysqli_fetch_array($result)){
        $_SESSION['searchedGroupRelationsUserID'] = $row['userID'];
        $searchedGroupRelationsUserID = $_SESSION['searchedGroupRelationsUserID'];
        echo $searchedGroupRelationsUserID;
    }

    $query = "SELECT * FROM users WHERE userID = '$searchedGroupRelationsUserID'";
    $result = mysqli_query($link, $query);

    while($row = mysqli_fetch_array($result)){
        echo $row['email'];
    }

Right now when I run the code by searching for a group by group name it is outputting "[email protected]" without quotes. This corresponds to the fact that users with userID's 17 and 18 are in the searched group, and userID 17 has the email [email protected].

1) Why is it not outputting user 18's email as well, if it is successfully outputting userID?

2) How do I separate the values into an array so I can choose to display the emails in a table with a separate row for each user?

you really need to do all of this in a single query, using joins — pala_

Luke Luke · Accepted Answer · 2015-04-17T03:33:56

1)

When you run the following query, you are selecting only a single record. I think that may actually be user 18's email address? It's the email address of the last result fetched in the previous loop.

SELECT * FROM users WHERE userID = '$searchedGroupRelationsUserID'

Best bet would be to have a single query to select the users:

SELECT * FROM users AS u INNER JOIN relations AS r ON u.userID = r.userID WHERE r.groupID = '$searchedGroupID'

Then iterate those results to fetch the fields you want.

2)

What you're doing is a good start! I'd suggest something like this:

echo "<table><tr><th>ID</th></tr>";
while ($row = mysqli_fetch_array($result)) {
    echo "<tr><td>" . $row['userID'] . "</td><td>" . $row['email'] . "</td></tr>";
}
echo "</table>";

Tangential points

I'd be using PDO rather than mysql_* functions, and preparing statements. mysql_* use is strongly discouraged, and the code as it stands is vulnerable to SQL injection.
You ideally want to split your display code and logic code. Often, this is done with templates. You'll have code somewhere that gets users (for example, function getUsersInGroup($groupID) { ... }), and you'll pass that to a template engine that renders something like ... {{ foreach user in users }} ... {{ user.userID }} ... {{ user.email }} ... .
I don't think that you'd usually use session variables for this sort of thing. Using $_GET means that the group ID would be in the URL, and so it's possible to share, favourite, etc the page for the users in a group.

How to display specific rows/columns in a many-to-many PHP/SQL relationship?

1 Answers