如何在codeigniter中使用多个数组正确显示数据

Question

First of all, I am apologize if my post is a double post. I googling every possible keywords to find answers but I am still no luck. I have data in my database that look like this:

Team table

id_team | team  | group
----------------------
      1 | Eagle | A
      2 | Lion  | B
      3 | Tiger | C
      4 | Leo   | D

Team member table

id_member | id_team | name        | number | tot_score
-----------------------------------------------------
        1 |       1 | Andrew C.   |     11 |        10
        2 |       1 | Thomas Gab. |      6 |         8
        3 |       1 | Clinton     |      8 |        13
        4 |       2 | Richardson  |     10 |         7
        5 |       2 | Sloane      |      7 |         4
        6 |       3 | Harris      |      8 |         3
        7 |       4 | Garry       |     10 |         8

I want to format the data like so:

<?php foreach($teams AS $team): ?>
  <div class="col-md-4">
    <table>
      <tr>
        <td><?php echo $team->team; ?></td>
        <td><?php echo $team->group; ?></td>
      </tr>

     <?php foreach($team AS $member): ?>
       <tr>
         <td><?php echo $member->number; ?></td>
         <td><?php echo $member->name; ?></td>
         <td><?php echo $member->tot_score; ?></td>         
       </tr>
     <?php endforeach;

    </table>    
  </div>
<?php endforeach; ?>

I am able to make the selection and joining the table, but I don't know how to achieve it. Does anyone can help? Thank you in advance

Answer 1

Try this in your model file:

SELECT T.id_team, T.team, T.group M.number, m.name, m.tot_score FROM [TEAM_TABLE] T
JOIN [TEAM_MEMBER_TABLE] M ON t.id_team = m.id_team

If you need the create the query with CodeIgniter then please have a look on this link.

This will return the data with whole information. You can use it with one foreach.

<?php foreach($teams AS $team): ?>
  <div class="col-md-4">
    <table>
      <tr>
        <td><?php echo $team->team; ?></td>
        <td><?php echo $team->group; ?></td>
        <td> <?= $team->number ?> </td>
      </tr>