php - sql join repeating results -

php - sql join repeating results -

i need create list printing clients ordered gym name, it's repeating gym name same number of gym's clients. if gym1 have 4 clients, echo printed 4 times.

the tables/columns are:

members (id, gym, name, etc...)

and

gym (gymid, gym_name, etc...).

member.gym know gym client belongs (gym.gymid)

if ($stmt = $mysqli->prepare(" select distinct g.*, m.* gym g inner bring together members m on m.gym = g.gymid")) { $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_array()) { echo 'sport center: ' . $row['gym_name'] . '<br>'; // here print gym's clients list } }

distinct not working... problem??

that's normal behavior.

example.

consider next tables

class="lang-none prettyprint-override">table "gym" ----------- gym_id | gym_name -------+---------- 1 | gym 2 | gym b table "members" --------------- member_id | gym_id | member_name ----------+--------+------------ 1 | 1 | bob 2 | 1 | jeff

and now, execute query:

class="lang-sql prettyprint-override">select g.gym_id, g.gim_name, m.member_id, m.member_name gym g inner bring together members m on g.gym_id = m.gym_id;

result:

class="lang-none prettyprint-override">gym_id | gym_name | member_id | member_name -------+----------+-----------+------------- 1 | gym | 1 | bob 1 | gym b | 2 | jeff

that happens because each row in gym table matched row in members name. if utilize select distinct, result same, because every row different.

i think want output this:

class="lang-none prettyprint-override"> gym bob jeff

although can done straight in sql, it's easier handle straight php, because doing in sql would real pain in neck require writing quite complex query. i'm not quite php, this:

class="lang-php prettyprint-override">/* don't need "distinct", need "order by" create work */ if ($stmt = $mysqli->prepare(" select g.*, m.* gym g inner bring together members m on m.gym = g.gymid order g.gymid")) { $stmt->execute(); $result = $stmt->get_result(); $gym = ""; while ($row = $result->fetch_array()) { if($row['gym_name'] != $gym) echo 'sport center: ' . $row['gym_name'] . '<br>'; echo ' member: ' . $row['member_name'] . '<br>'; $gym = $row['gym_name']; } }

php mysql sql