CODEWITHSUNDEEP
mysql
PhpDude
PhpDude

Reputation: 1598

Get members from each team via SQL JOINS

I have 3 tables, members, teams & team_members.

team_members houses the ids from both the members & teams and stores them per row.

The schema of theteam_members table:

enter image description here

teams table:

What I'd like to be able to do is create a join where i can output the team_name from the teams table and then underneth show the members_firstname associated with that that team. So example:

Team 1
joe bloggs
jon doe

Team 2
charlie chaplin
hulk hogan

My php code looks like this:

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$sql = "SELECT t.team_name, group_concat(m.firstName) 
        FROM members AS m
        JOIN team_members AS tm
        ON tm.member_id = m.member_id 
        JOIN teams as t
        on t.team_id = tm.team_id
        GROUP BY t.teamname"; 

$result = $conn->query($sql);
    if($result->num_rows > 0){
        while($row = $result->fetch_assoc()){
            echo $row["member_id"] . '<br>';
            echo $row["team_id"] . '<br><br>';      
        }
    }
?>

Now I get:

Notice: Trying to get property of non-object in -- on line 30 which is:

if($result->num_rows > 0){

Upvotes: 0

Views: 169

Answers (2)

chris85
chris85

Reputation: 23892

You should use two joins to piece together the three tables.

SELECT t.team_name as team_name, group_concat(m.firstName) as team_members 
FROM members AS m
JOIN team_members AS tm
ON tm.member_id = m.member_id 
JOIN teams as t
on t.team_id = tm.team_id
GROUP BY t.team_name

You then should check the status of your query before trying to work with it.

if(!$result = $conn->query($sql)) {
     die(printf("Errormessage: %s\n", $conn->error));
}

Then loop through the results, and split the grouped values by the comma.

while($row = $result->fetch_assoc()){
      echo $row["team_name"] . '<br>';
      $names = explode(',', $row['team_members']);
      foreach($names as $name) {
           echo $name . '<br>';
       }
       echo '<br>';      
}

You also could use <br> as the separator in the group_concat. You can read more about that function here, http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat. If you did that you can get rid of the foreach and explode because the $row['team_members'] would be built with a linebreak for each member.

Upvotes: 1

BRap
BRap

Reputation: 625

The easy way to do this in PHP is width two nested loops in first you go trought the team table and print it in the second you print all the playes width theat team id...

Something like:

 do{
      echo $row_team;

      do{
           echo $row_playa;
      }while($row_plya = mysql_fetch_assoc($playas));    // whear pleya.team_id = $row_team.id

 }while($row_team = mysql_fetch_assoc($teams));

Upvotes: 0

Related Questions