Codeigniter - 将三个表合并为正确的视图格式 [英] Codeigniter - Combining Three tables into correct view format

问题描述

完全混淆了如何实现某些内容 - 我有三个表格：

• 订单


• 项目


• 产品

订单表包含订单的详细信息，产品ID号和订单ID号，产品表包含所有产品详细信息。

我想要做的是有一个我的订单页

ORDERS ，您可以查看所有过去的订单，我想要的是能够拥有以下结构。

订单号1

• 第二个示例订单项


• 第三个示例订单项

订单编号2

• 订单项示例


• 示例订单项


• 第三个示例订单项

订单号3

• 示例订单项


• 第二个示例订单项


• 第三个示例订单项

现在我可以轻松地列出所有订单，但我迷失在如何合并这三个表在一个方式，我可以列出他们在上述格式，我假设我可以JOIN所有三个，但我不能让他们在任何地方以正确的方式输出。 / p>

有人知道这样做的好方法吗？非常感谢。

解决方案

您可以使用连接的查询来获取所有订单

  / *原始sql查询* / 
 SELECT o。*，p。*，o.id AS order_id，p.id AS product_id 
 FROM orders o 
 LEFT JOIN项目i ON（o.id = i.order_id）
 LEFT JOIN产品p ON（p.id = i.product_id）
 ORDER BY order_id 
 

在模型中创建一个函数并在查询下运行

  / *使用活动记录* / 
 
 $ 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（）; 
  

从控制器中的模型获取结果，然后将其传递给查看，查询记录为

  $ 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>'; 
} 
  

这将输出为

 < h1> ORDER NUMBER 1< / h1> 
< p>产品1< / p> 
< p>产品2< / p> 
< h1> ORDER NUMBER 2< / h1> 
< p>产品3< / p> 
 

如果你有不同的html结构改变它，因此我已经提供了上面的例子使用标题和段落标签


$ b code> 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>';


这将输出为

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

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.

解决方案

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>

这篇关于Codeigniter - 将三个表合并为正确的视图格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！