+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Codeigniter 4 display records using join with multiple tables (/showthread.php?tid=77591)
Codeigniter 4 display records using join with multiple tables - Dr3am3rz - 09-21-2020
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 :
- all the teams that test3 is in
- all the members that are in the displayed teams
- under the edit, will pull the team id to create an url
- if the current logged in user is the team leader, will display "remove member" except himself
- 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]
RE: Codeigniter 4 display records using join with multiple tables - ojmichael - 09-21-2020
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']
RE: Codeigniter 4 display records using join with multiple tables - Dr3am3rz - 09-22-2020
(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.