php - MySQL多级数组中多个表的数据

Question

如果有人能提供帮助，那就太好了？我目前有下面的数据由底部的代码输出，但不是对每个 order_product_id 重复，我想从数据构建一个多级数组。

当前输出:

[0] => Array
        (
            [order_product_id] => 43
            [order_id] => 1
            [product_id] => 161
            [name] => Hoodie
            [model] => Hoodie
            [quantity] => 1
            [price] => 23.9500
            [total] => 23.9500
            [tax] => 0.0000
            [reward] => 0
            [option_id] => 141
            [option_name] => Hoodie Style
            [option_value] => Pull over
        )

    [1] => Array
        (
            [order_product_id] => 43
            [order_id] => 1
            [product_id] => 161
            [name] => Hoodie
            [model] => Hoodie
            [quantity] => 1
            [price] => 23.9500
            [total] => 23.9500
            [tax] => 0.0000
            [reward] => 0
            [option_id] => 142
            [option_name] => Hoodie Colour
            [option_value] => Light Pink
        )

    [2] => Array
        (
            [order_product_id] => 43
            [order_id] => 1
            [product_id] => 161
            [name] => Hoodie
            [model] => Hoodie
            [quantity] => 1
            [price] => 23.9500
            [total] => 23.9500
            [tax] => 0.0000
            [reward] => 0
            [option_id] => 143
            [option_name] => Adult Sizes
            [option_value] => Ladies Meduim 10-12
        )

所需的数组，其中每个产品都具有多级别的所有选项，而不是当前设置如何显示它:

[0] => Array
        (
            [order_product_id] => 43
            [order_id] => 1
            [product_id] => 161
            [name] => Hoodie
            [model] => Hoodie
            [quantity] => 1
            [price] => 23.9500
            [total] => 23.9500
            [tax] => 0.0000
            [reward] => 0
            Array
                (
                [0] => Array
                    (
                    [option_id] => 141
                    [option_name] => Hoodie Style
                    [option_value] => Pull over
                    )
                [1] => Array
                    (
                    [option_id] => 142
                    [option_name] => Hoodie Colour
                    [option_value] => Light Pink
                    )
                [2] => Array
                    (
                    [option_id] => 141
                    [option_name] => Adult Sizes
                    [option_value] => Ladies Meduim 10-12
                    )
                )
        )

当前输出是这样构建的:

$sql = "SELECT site_order_product.*, 
site_order_option.order_option_id AS option_id, site_order_option.name AS option_name, site_order_option.value AS option_value
FROM site_order_product
INNER JOIN site_order_option ON site_order_product.order_product_id = site_order_option.order_product_id; ";

$result=mysqli_query($dbh2,$sql);
  if($result) {
            $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $row;
        } else {
            return false;
        }   

感谢您的帮助!

Answer 1

NOTE : The feature that your looking is lazy loading. ORM suits best for your problem. To name some of ORM's RedBean, Doctrine, Propel.

This is an example just for the sake of demonstration.

如果没有任何正确的数据库表模式，就很难为您提供您正在寻找的确切答案输出。但会帮助您提供一些具有相同作用的示例。

认为您有产品表和订单表。

Products

id | product_name | product_price | product_notes

Orders

id | product_id | order_number

您面临的问题:现在，如果您查询产品，您将获得产品详细信息列表。但您面临的问题是也获得其各自的订单。

解决方案:当循环进入每个产品时，获取相应的产品 id 并查询该产品的所有订单并分配到一个主数组中。

/* Get the list of products */
$productsQuery = mysqli_query($link, "SELECT * FROM products"); 
/* Array to hold the details or products and its respective order per product */
$productDetails = array(); 

if(mysqli_num_rows($productsQuery) > 0){
    $productCount = 0;
    while($product = mysqli_fetch_assoc($productsQuery)){
        $productId = $product['id'];

        /* Assign the product details to productDetails array */
        $productDetails[$productCount] = $product;

        /* Get the details of orders which respective productid */
        $ordersQuery = mysqli_query($link, "SELECT * FROM orders WHERE product_id = ".$productId);
        if(mysqli_num_rows($ordersQuery) > 0){
            while($order = mysqli_fetch_assoc($ordersQuery)){
                /* Here we have assigned the product orders to respective product */
                $productDetails[$productCount]['orders'] = $order;
            }
        }else{
            /* If no order assign an empty array */
            $productDetails[$productCount]['orders'] = [];
        }
    }
}