• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Codeigniter 4 display records using join with multiple tables

#1
I am trying to display a team list with the list of members associated with their respective teams but I can't seem to get it working. I keep getting duplicates or displaying separate members for the same team. My current code is as below:

I have insert my current database into [color=var(--blue-700)]sql fiddle[/color]

[color=var(--blue-700)]Model[/color]


Code:
return $this->db->table('team_list t1')
                        ->join('teammember_list t2', 't2.team_id = t1.team_id')
                       
                        ->join('game_list t3', 't3.game_id = t2.game_id')
                        ->join('add_game t4', 't4.game_id = t3.game_id')
                        ->where('t2.user_id', $user_id)
                        ->get()
                        ->getResultArray();


[color=var(--blue-700)]Controller[/color]


Code:
$db = db_connect();
$model = new TeamsModel($db);
$data['teams'] = $model->getTeams();

session()->set($data);


[color=var(--blue-700)]View[/color]


Code:
<table class="table teams-list">
            <thead class="thead-light">
                <tr>
                    <th scope="col">Logo</th>
                    <th scope="col">Team Name</th>
                    <th scope="col">Game</th>
                    <th scope="col">Leader IGN</th>
                    <th scope="col">Members IGN</th>
                    <th scope="col">Edit</th>
                </tr>
            </thead>
            <tbody>

        <?php foreach($teams as $row) : ?>
             <tr>
                <th scope="row"><img src="<?php echo base_url('/public/uploads/img/'. strtolower($row['team_logo'])); ?>" alt="" /></th>
                <td><?= $row['team_name']; ?></td>
                <td>
                    <img src="<?php // echo base_url('/public/img/logo_'. strtolower($row['game_name']) .'.jpg'); ?>" alt="" />
                    <br />
                    <?//= $row['game_name']; ?>
                </td>
                <td><?//= $row['ign']; ?></td>
                <td>
                <?php if($row['user_id'] == session()->get('user_id')) { ?>
                    <?= $row['ign']; ?><a href="teammember_remove/<?= $row['team_id']; ?>" target="_top">Remove</a>
                    <?//= $row['ign']; ?>
                    <br />
                    <a href="teammember_reg/<?= $row['team_id']; ?>" target="_top">Add Members</a>
                <?php } else { ?>
                    <?= $row['ign']; ?><a href="teammember_remove/<?= $row['team_id']; ?>" target="_top">Leave</a>
                <?php } ?>
                </td>
                <td>
                <?php if($row['user_id'] == session()->get('user_id')) { ?>
                    <a href="edit_game/<?= $row['team_id']; ?>" target="_top">Edit</a>
                <?php } ?>
                </td>
            </tr>
           

        <?php endforeach; ?>

        </tbody>
    </table>



If the current logged in username is test3, I want it to display like :
  1. all the teams that test3 is in

  2. all the members that are in the displayed teams

  3. under the edit, will pull the team id to create an url

  4. if the current logged in user is the team leader, will display "remove member" except himself

  5. if the current logged in user is not the team leader, will only have the option of "leave team"
Code:
team logo | team name      | game | leader ign    | member ign              | edit
image     | dotatest3team  | dota | dotafortest3  | dotafortest1 (remove)   |
                                                  | dotafortest2 (remove)   |
                                                  | dotafortest3            | team id 11
                                                  | dotafortest11 (remove)  |
                                                  | dotafortest12 (remove)  |

image     | codtest2team  | cod   | dotafortest2  | dotafortest1            |
                                                  | dotafortest2            |
                                                  | dotafortest3 (leave)    |
                                                  | dotafortest11           |
                                                  | dotafortest12           |

[color=var(--blue-700)]Hope someone can help me here. Thanks in advance guys![/color]
Reply

#2
You have a few options here.. recursive SQL (slow), some hideous GROUP_CONCAT or a better proposed solution below.

PHP Code:
$teams $this->db->table('team_list t1')
    ->join('teammember_list t2''t2.team_id = t1.team_id')
    ->join('game_list t3''t3.game_id = t2.game_id')
    ->join('add_game t4''t4.game_id = t3.game_id')
    ->where('t2.user_id'$user_id)
    ->get()
    ->getResultArray();

$teamIds array_column($teams'team_id');

$teamMembers $this->db->table('teammember_list t2')
    ->whereIn('t2.team_id'$teamIds)
    ->get()
    ->getResultArray();

foreach (
$teams AS &$team) {
    $team['members'] = array_filter(
        $teamMembers,
        fn($teamMember) => $teamMember['team_id'] === $team['team_id']
    );


Then you can loop through your teams for the table rows, and in the member column you can loop through the $team['members']
Reply

#3
(09-21-2020, 11:39 PM)ojmichael Wrote: You have a few options here.. recursive SQL (slow), some hideous GROUP_CONCAT or a better proposed solution below.

PHP Code:
$teams $this->db->table('team_list t1')
    ->join('teammember_list t2''t2.team_id = t1.team_id')
    ->join('game_list t3''t3.game_id = t2.game_id')
    ->join('add_game t4''t4.game_id = t3.game_id')
    ->where('t2.user_id'$user_id)
    ->get()
    ->getResultArray();

$teamIds array_column($teams'team_id');

$teamMembers $this->db->table('teammember_list t2')
    ->whereIn('t2.team_id'$teamIds)
    ->get()
    ->getResultArray();

foreach (
$teams AS &$team) {
    $team['members'] = array_filter(
        $teamMembers,
        fn($teamMember) => $teamMember['team_id'] === $team['team_id']
    );


Then you can loop through your teams for the table rows, and in the member column you can loop through the $team['members']

Hi, thank you so much for your help! But how do I do that? Do I return teams? or do I return teamIds? Sorry I am new to this and I still don't know how all these works.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.