php - Codeigniter - Combining Three tables into correct view format

Question

Completely confused on how to achieve something - I have three tables;

• Orders
• Items
• Products

The orders table contains the details of an order, the items table contains a product id number and an order id number, and the products table contains all the product details.

What I'm trying to do is have a "My orders" page, where you can view all your past orders, and what i would like is to be able to have the following structure...

ORDERS

ORDER NUMBER 1

• Example order item
• Second example order item
• Third example order item

ORDER NUMBER 2

• Example order item
• Second example order item
• Third example order item

ORDER NUMBER 3

• Example order item
• Second example order item
• Third example order item

Now i can easily list all the orders, and i can list all the items on a particular order, but I'm lost on how to merge these three tables in a way i can list them in the above format, i assume i can JOIN on all three, but i can't get them to output in anywhere near the correct way.

Does anybody know a good way of doing this? Many thanks in advance.

Answer 1

You can use a joined query to get all orders with their items

/* raw sql query */
SELECT o.*,p.*,o.id AS order_id,p.id AS product_id
FROM  Orders o 
LEFT JOIN Items i ON (o.id=i.order_id)
LEFT JOIN Products p ON(p.id = i.product_id) 
ORDER BY order_id

Create a function in your model and run below query

/* Using Active record */

$orders=$this->db->select('o.*,p.*,o.id AS order_id,p.id AS product_id')
                ->from(' Orders o ')
                ->join(' Items i','o.id=i.order_id','LEFT')
                ->join(' Products p','p.id = i.product_id','LEFT')
                ->order_by('order_id')
                ->get()
                ->result();

Get your results from model in controller and then pass it to view,in view you can loop over your query records as

$currentParent = false;
foreach ($orders as $o) {
    if ($currentParent != $o->order_id) {
        echo '<h1>ORDER NUMBER ' . $o->order_id . '</h1>';
        $currentParent = $o->order_id;
    }
    echo '<p>' . htmlentities($o->product_name) . '</p>';
}

This will output as

<h1>ORDER NUMBER 1</h1>
    <p>product 1</p>
    <p>product 2</p>
<h1>ORDER NUMBER 2</h1>
    <p> product 3</p>

If you have different html structure change it accordingly i have provided above example using heading and paragraph tag

Edit for using list tags in your you can do so

echo '<ul><li>';
$currentParent = false;
foreach ($orders as $o) {
    if ($currentParent != $o->order_id) {
        if ($currentParent != false) {
            echo '</ul></li><li>';
        }
        echo '<h1>ORDER NUMBER ' . $o->order_id . '</h1><ul>';
        $currentParent = $o->order_id;
    }
    echo '<li>' . htmlentities($o->product_name) . '</li>';
}
echo '</li></ul>';

This will output as

<ul>
    <li>
        <h1>ORDER NUMBER 1</h1>
        <ul>
            <li><p>product 1</p></li>
            <li><p>product 2</p></li>
        </ul>
    </li>
    <li>
        <h1>ORDER NUMBER 2</h1>
        <ul>
            <li><p> product 3</p></li>
        </ul>
    </li>
</ul>