PHP SQL - 从另一个表中检索不是预期的共享值

Question

I'm needing to retrieve shared values from a table based on a value from another table, but don't show duplicates.

Example of what tables I have...

Table - members

+-----------------+
| ID    | NAME    |
+-----------------+
| 1     | Bob     |
| 2     | Jack    |
| 3     | Jane    |
| 4     | Bruce   |
| 5     | Clark   |
| 6     | Peter   |
+-----------------+

Table - groups

+--------------------------------+
| ID    | NAME      | MANAGER_ID |
+--------------------------------+
| 1     | Group A   | 1          |  (Bob)
| 2     | Group B   | 2          |  (Jack)
| 3     | Group C   | 1          |  (Bob)
+--------------------------------+

Table - group_members

+--------------------------------+
| ID    | GROUP_ID  | MEMBER_ID  |
+--------------------------------+
| 1     |   1       |  3         |  (Group A - Jane)
| 2     |   1       |  4         |  (Group A - Bruce)
| 3     |   1       |  5         |  (Group A - Clark)
| 4     |   1       |  6         |  (Group A - Peter)
| 5     |   2       |  3         |  (Group B - Jane)
| 6     |   3       |  4         |  (Group B - Bruce)
| 7     |   3       |  5         |  (Group C - Clark)
+--------------------------------+

What I am needing

(Note: I'm using * in queries here to shorten code.)

If 'Bob' sees all his groups.

Look at 'group_members' table and show all members that belong to it...

$q = SELECT * FROM groups WHERE manager_id = $id;
$r = mysqli_query($dbc, $q);
while ($row = mysqli_fetch-assoc($r) {
    $q2 = SELECT *, count(MEMBERS_ID) AS group_count FROM group_members LEFT JOIN members ON group_members.MEMBER_ID = members.id WHERE group_id = '$row[GROUP_ID]';
    $r2 = mysqli_query($dbc, $q2);
    while ($row2 = mysqli_fetch-assoc($r2) {
        echo $row2['name'];
    } 
}

This shows me the list as expected.

+------------------------+
| NAME    | GROUP COUNT  |
+------------------------+
| Jane    | 1            |
| Bruce   | 1            |
| Clark   | 1            |
| Peter   | 1            |
| Bruce   | 1            |
| Clark   | 1            |
+------------------------+

I Add GROUP BY group_members.group_id to my 2nd query and that shows.

+------------------------+
| NAME    | GROUP COUNT  |
+------------------------+
| Jane    | 1            |
| Bruce   | 2            |
| Clark   | 2            |
| Peter   | 1            |
+------------------------+

Which is perfect... But here is the problem

if I add a WHERE members.name LIKE \'%clark%\' then it outputs...

+------------------------+
| NAME    | GROUP COUNT  |
+------------------------+
|         |              |
|         |              |
| Clark   | 1            |
|         |              |
|         |              |
| Clark   | 1            |
+------------------------+

It ignores GROUP BY and shows blank rows where other entries would show.

So with all that said. Does any one know why or a better way to do this please?

Been at it for a while now and would really appreciate any assistance.

EDITED:

Here's the full query with all the columns used:

$q = SELECT * FROM groups WHERE manager_id = $id;
$r = mysqli_query($dbc, $q);
while ($row = mysqli_fetch-assoc($r) {
    $q2 = SELECT members.id) AS memid, members.first, members.last, members.comname, members.email, members.sector, (members.status) AS memstatus, (group_members.id) AS groupid, (group_members.member_id) AS memidgroup, group_members.group_id, COUNT(group_members.member_id) AS groupcount, member_roles.role FROM members LEFT JOIN group_members ON members.id = group_members.member_id LEFT JOIN member_roles ON members.role_id = member_roles.id WHERE group_id = '$row[GROUP_ID]' AND members.name LIKE '%clark%' GROUP BY group_members.group_id;
    $r2 = mysqli_query($dbc, $q2);
    while ($row2 = mysqli_fetch-assoc($r2) {
        echo $row2['name'];
    } 
}

Answer 1

Your query or problem is not completely stated. One cannot guess or assume. Post your last query as well as all queries dont worry about saving the space. Those blank rows have data that why they are in the table.

Base on your explanation or your query, here is my simple answer

SELECT id,
(select groups.id from groups where groups.id = group_members.group_id )AS group_members_id, 
(select groups.name from groups where groups.id = group_members.group_id )AS group_members_name,  
(select members.id from members where members.id =  group_members.member_id )AS members_id, 
(select members.name from members where members.id =  group_members.member_id )AS members_name,
count((select members.id from members where members.id =  group_members.member_id ) )as members_id_count FROM group_members WHERE (select members.name from members where members.id =  group_members.member_id ) LIKE '%clark%' group by members_id

As you mentioned

WHERE members.name LIKE \'%clark%\'

you were selecting data from the members table whereas you have to select the data from group_members table.