时间戳

首页 > 解决方案 > 创建一个从 mysqli 查询的结果分成两部分的数组

php - 创建一个从 mysqli 查询的结果分成两部分的数组

问题描述

我不确定我想要实现的正确术语（多维？），所以我将向您展示我的代码，并尝试解释。我在 zen cart 中工作，这就是数据库查询使用 $db->e​​xecute 的原因。

以下是构建我的数组的函数

   $oID = (int)$oID;
   $combined =  $db->execute("SELECT op.orders_id, op.products_model,
                    op.products_name, op.products_quantity,
                    o.customers_name
                    FROM " . TABLE_ORDERS_PRODUCTS . " op
                    LEFT JOIN " . TABLE_ORDERS . " o
                    ON op.orders_id = o.orders_id
                    WHERE op.orders_id = $oID" . $this->product_filter);

    while (!$combined->EOF) {
        $order_id = $combined->fields['orders_id'];
        $customers_name = $combined->fields['customers_name'];
        $products_name = $combined->fields['products_name'];
        $products_model = $combined->fields['products_model'];
        $products_quantity = $combined->fields['products_quantity'];

        $this_order_data = array(
                               'order' => $order_id,
                               'customer' => $customers_name,
                               'name' => $products_name,
                               'model' => $products_model,
                               'quantity' => $products_quantity
                              );
    echo'<pre>';print_r($this_order_data);echo'</pre>';
    $combined->MoveNext();
    }

这给了我这样的结果

Array
(
[order] => 1913
[customer] => Customer A
[name] => Product 1
[model] => P1
[quantity] => 15
)
Array
(
[order] => 1913
[customer] => Customer A
[name] => Product 2
[model] => P2
[quantity] => 15
)
Array
(
[order] => 1912
[customer] => Customer B
[name] => Product 3
[model] => P3
[quantity] => 5
)
Array
(
[order] => 1911
[customer] => Customer C
[name] => Product 2
[model] => P2
[quantity] => 75
)
Array
(
[order] => 1911
[customer] => Customer C
[name] => Product 4
[model] => P4
[quantity] => 30
)
Array
(
[order] => 1910
[customer] => Customer D
[name] => Product 5
[model] => P5
[quantity] => 31
)

我真正想要的是更像这样

Array
(
[order] => 1911
[customer] => Customer C
) 
    Array
    (
    [name] => Product 2
    [model] => P2
    [quantity] => 75
    )
    Array
    (
    [name] => Product 4
    [model] => P4
    [quantity] => 30
    )
Array
(
[order] => 1910
[customer] => Customer D
)
    Array
    (
    [name] => Product 5
    [model] => P5
    [quantity] => 31
    )

所以本质上，我的部分 sql 结果（订单和客户）形成一个数组，然后为该订单下的每个行项重复名称、型号和数量。然后它继续下一个订单。

如何拆分我的 sql 结果以形成两个数组，一个在另一个数组中？

编辑：最终目标是能够回显显示订单 ID 和客户名称的报告，然后是订单包含的产品列表。

标签： php

解决方案

如果 orders_id 是主键，TABLE_ORDERS您可以像这样修改代码：

 $result = []; //somewhere early, before while, initialize empty array

然后：

 //replace from $this_order_data = array( ...
 $this_order_data = array(
                           'name' => $products_name,
                           'model' => $products_model,
                           'quantity' => $products_quantity
                         );
if (isset($result[$order_id])) {
  $result[$order_id]['products'][] = $this_order_data;
} else {
  $result[$order_id]['order_id'] = $order_id;
  $result[$order_id]['customer_name'] = $customer_name; //here we need the primary key
                                                    //exactly one customer per order
  $result[$order_id]['products'][] = $this_order_data;
}
$combined->MoveNext();
} //end while

var_dump(array_values($result)); //you can remove array_values, 
      //if you don't mind that the order ids are array keys.

推荐阅读