CODEWITHSUNDEEP
phpmysqlcodeigniterforeachfetch
Rizky Irmawan
Rizky Irmawan

Reputation: 67

How to fetch relational data in CodeIgniter

I need to fetch two relational tables with foreach method in CodeIgniter. This only an example codes I provide in my question, I will surely need this for my future projects and I'm totally blank about what kind of query to do this.

Here is buyer table:

+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
+----+------+

Here is goods table:

 +----+----------+------------+
 | id | buyer_id | goods_name |
 +----+----------+------------+
 |  1 |        1 | Apple      |
 |  2 |        1 | Grape      |
 |  3 |        1 | Banana     |
 |  4 |        2 | Pear       |
 |  5 |        2 | Mango      |
 +----+----------+------------+

In my model file mtest:

public function buyer_list()
{
    $this->db->select('*');
    $this->db->from('buyer');
    $query = $this->db->get();
    return $query->result();
}


public function order_list()
{
    $this->db->select('*');
    $this->db->from('goods');
    $this->db->join('buyer', 'buyer.id = goods.buyer_id', 'inner');
    $query = $this->db->get();
    return $query->result();
}

In my controller:

public function index()
{
    $this->load->model('mtest');

    $data['buyer']  =   $this->mtest->buyer_list();
    $data['order']  =   $this->mtest->order_list();

    $this->load->view('example', $data);
}

In my view file example:

<div id="container">
   <?php foreach ($buyer as $buyer_list): ?>
    <strong><?php echo $buyer_list->name ?></strong>: 
    <ul>
        <?php foreach ($order as $order_list): ?>
        <li><?php echo $order_list->goods_name ?></li>
        <?php endforeach; ?>
    </ul>
  <?php endforeach; ?>
</div>

That codes above only showed me the entire row of goods table. I want a result of something like this:

  +------+------------+
  | John | : • Apple  |
  |      |   • Grape  |
  +      +   • Banana +
  |      |            |
  + Jane + : • Pear   +
  |      |   • Mango  |
  +------+------------+

Sorry for my bad english. Any ideas?

Upvotes: 1

Views: 448

Answers (1)

Andrei Lupuleasa
Andrei Lupuleasa

Reputation: 2762

In controller do this:

public function index()
{
    $this->load->model('mtest');

    $orders  =   $this->mtest->order_list();


    $sortedOrders = [];
    foreach($orders as $order){
        $sortedOrders[$order->name][] = $order;
    }

    $this->load->view('example', [ "sortedOrders" => $sortedOrders ]);
}

In view:

<div id="container">
   <?php foreach ($sortedOrders as $k=>$sortedOrder): ?>
    <strong><?php echo $k ?></strong>: 
    <ul>
        <?php foreach ($sortedOrder as $goods): ?>
        <li><?php echo $goods->goods_name ?></li>
        <?php endforeach; ?>
    </ul>
  <?php endforeach; ?>
</div>

Upvotes: 2

Related Questions