我正在尝试获取按产品分组的多维数组。
这是我尝试过的sql
:
$sql = "SELECT * from order_detail OD'n"
. "LEFT JOIN product as pro ON pro.product_id = OD.product_id'n"
. " LEFT JOIN `order` as o ON o.order_id = OD.order_id 'n"
. "WHERE OD.order_id IN (1,4) AND OD.user_id =2'n"
. "group by OD.product_id'n"
. "order By OD.Product_ID, OD.Order_ID";
这个sql给出以下结果:
Array
(
[0] => Array
(
[order_detail_id] => 1
[user_id] => 2
[order_id] => 1
[product_id] => 13
[product_name] => Prodict Test
)
[1] => Array
(
[order_detail_id] => 2
[user_id] => 2
[order_id] => 1
[product_id] => 12
[product_name] => Product One
)
[2] => Array
(
[order_detail_id] => 3
[user_id] => 2
[order_id] => 1
[product_id] => 11
[product_name] => Fodod
)
[3] => Array
(
[order_detail_id] => 34
[user_id] => 2
[order_id] => 4
[product_id] => 13
[product_name] => Prodict Test
)
[4] => Array
(
[order_detail_id] => 35
[user_id] => 2
[order_id] => 4
[product_id] => 12
[product_name] => Product One1
)
[5] => Array
(
[order_detail_id] => 36
[user_id] => 2
[order_id] => 4
[product_id] => 11
[product_name] => Fodod
)
)
这就是我想要的结构:
Array
(
[0] => Array
(
[product_id] => 13
[product_name] => Prodict Test
['order'] => Array
(
[0] => Array
(
[order_detail_id] => 1
[user_id] => 2
[order_id] => 1
)
[1] => Array
(
[order_detail_id] => 34
[user_id] => 2
[order_id] => 4
)
)
)
[1] => Array
(
[product_id] => 12
[product_name] => Product One
['order'] => Array
(
[0] => Array(
[order_detail_id] => 2
[user_id] => 2
[order_id] => 1
)
[0] => Array(
[order_detail_id] => 35
[user_id] => 2
[order_id] => 4
)
)
)
)
有人能建议我怎么做吗?
这是我的桌子结构
订单表
+----------+---------+------------+
| order_id | user_id | invoice_no |
+----------+---------+------------+
| 1 | 2 | INV-1 |
| 2 | 3 | INV-2 |
| 3 | 5 | INV-3 |
| 4 | 2 | INV-4 |
| 5 | 5 | INV-5 |
| 6 | 3 | INV-6 |
+----------+---------+------------+
订单明细表
+-----------------+---------+----------+------------+
| order_detail_id | user_id | order_id | product_id |
+-----------------+---------+----------+------------+
| 1 | 2 | 1 | 13 |
| 2 | 2 | 1 | 12 |
| 3 | 2 | 1 | 11 |
| 4 | 2 | 1 | 9 |
| 5 | 2 | 1 | 8 |
| 6 | 2 | 1 | 7 |
| 7 | 2 | 1 | 6 |
| 34 | 2 | 4 | 13 |
| 35 | 2 | 4 | 12 |
| 36 | 2 | 4 | 11 |
| 37 | 2 | 4 | 9 |
+-----------------+---------+----------+------------+
prodcut表
+------------+---------------+
| product_id | product_name |
+------------+---------------+
| 1 | Product One |
| 2 | Product Two |
| 3 | Product Three |
| 4 | Product Four |
| 5 | Product Five |
| 6 | Product Six |
| 7 | Fodod |
| 8 | Pepsi |
| 9 | Product Onea |
| 10 | Fodod |
| 11 | Fodod |
| 12 | Product One1 |
| 13 | Prodict Test |
+------------+---------------+
注意:我已经尝试了以下两种解决方案,但对我的都不起作用
解决方案1
解决方案2
使用左联接建立循环关联。使内环&参加像这样管理您的查询
$this->db->select('*');
$this->db->from('exp_incoming_order');
$this->db->order_by('id','desc');
$this->db->where('member_id',12);
$query1 = $this->->db->get();
$data=array();
foreach($query1->result_array() as $key_1=>$result)
{
foreach($result as $key=>$value1)
{
$data[$key_1][$t1][$key]=$value1;
}
}
foreach($data as $single=>$part) {
foreach($tables as $table=>$value2) {
$this->db->select('*');
$this->db->from('order_detail');
$this->db->where('order_id',$part['exp_incoming_order']['order_id']);
$query2 = $this->db->get();
foreach($query2->result_array() as $key2=>$result2)
{
foreach($result2 as $key3=>$value3)
{
$data[$single][$table][$key2][$key3]=$value3;
}
}
}
}