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?
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