问题描述
完全混淆了如何实现某些内容 - 我有三个表格:
- 订单
- 项目
- 产品
订单表包含订单的详细信息,产品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 - 将三个表合并为正确的视图格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!