获取具有它们之间关系的行

Question

I have table in my database containing relations between clients companies. Below is just a part from the existing table.

+----+---------+---------+
| ID | Client1 | Client2 |
+----+---------+---------+
| 18 |     250 |      77 |
| 19 |     250 |     317 |
| 13 |     317 |      12 |
|  5 |      23 |      12 |
+----+---------+---------+

When seeing client 250 I can show also clients 77 and 317. I want to be able to fetch the ids of the other clients in the chain so that when my user opens client 250 to see the ids of 77, 317, 12 and 23. Is there a way to fetch all the ids with one mysql query or I should have some other data processing. Below is the query and some checks for the current situation:

$sql = "SELECT cc.Id, cc.Client1Id, cc.Client2Id, cc.Note
            FROM ClientConnections as cc
            WHERE cc.Client1Id  = ? OR cc.Client2Id = ?";
            $stmt = $this->db -> prepare($sql);
            $stmt -> bind_param('ii', $id, $id);
            $stmt -> execute();
            $stmt -> bind_result($connectionId, $client1Id, $client2Id, $note);
            $stmt -> store_result();

        while($stmt -> fetch()){

            //if found client has the same id as the current client
            if ($client1Id == $id) {
                $clientId = $client2Id;
            }elseif ($client2Id == $id) {           
                $clientId = $client1Id;
            }

            $name = $this->getClient($clientId); //getting client info

            $array[] = array(
                'id' => $connectionId,
                'client_id' => $clientId,
                'name' => $name['name'],
                'payment_status' => $name['payment'],
                'note' => $note
            );

        }

If there is a query which can done the job will be perfect, but if there isn't I'll be happy someone to give me just an idea how to do that. Maybe a recursion? I'm stuck.

Answer 1

Try this answer.

The guy is using GROUP_CONCAT to Group the rows. You can use it on your query to group your rows based on Client 1.

  SELECT k.id, GROUP_CONCAT(d.value ORDER BY d.name separator ' ')
  FROM keywords AS k
  INNER JOIN data as d ON k.id = d.id
  GROUP BY k.id

The problem is that he is using two tables to do that, so you can either split your tables or you can do INNER JOIN (SELECT .. on the same table.