php - 多表 mysql/php 查询，两个输出到一个数组

Question

抱歉，这需要一点时间来解释。

这段代码是 opencart 的 xml vqmod 的一部分，它将订单数据从选定的订单导出到 Excel 电子表格。在大多数情况下它工作得很好。不过，有一个问题，如果某条记录中针对特定订单列出了多个高级优惠券，则该记录将会崩溃。

我确定我需要为其输出添加一个数组及其与折扣金额相关的字段，但我不确定如何做到这一点。我是 php/MySQL 的新手，但学得很快。如果我能指明正确的方向，那就太好了。

我是新来的，但在搜索中找不到类似的内容。再次感谢。

这是相关的代码:

    // The actual options data
    $i += 1;
    $j = 0;
      $query  = "SELECT o.*, op.name, op.model, op.order_product_id, op.quantity, op.price, op.total AS ptotal, op.product_id, op.tax, (SELECT amount FROM " . DB_PREFIX . "sales_promotion_history WHERE order_id = o.order_id) AS sales_promo_amount, (SELECT title FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'sales_promotion') AS sales_promo_code, (SELECT value FROM order_total ott WHERE ott.order_id = o.order_id AND ott.code = 'shipping') AS shipping_cost, (SELECT name FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id) AS `order_status`, (SELECT ot.value FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total') AS sub_total, (SELECT ot.value FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'credit') AS store_credit, (SELECT ot.title FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher') AS gift_code, (SELECT ot.text FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher') AS gift_value, (SELECT title FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'advanced_coupon') AS advanced_coupon_code, (SELECT amount FROM " . DB_PREFIX . "advanced_coupon_history WHERE order_id = o.order_id) AS ac_amount, (SELECT ot.title FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'coupon') AS coupon_code, (SELECT ot.text FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'coupon') AS coupon_value FROM `" . DB_PREFIX . "order` o inner join `" . DB_PREFIX . "order_product` op on o.order_id=op.order_id  WHERE o.order_id IN ( ".$orderid." ) order by o.order_id";

    $result = $database->query( $query );
    foreach ($result->rows as $row) {
        $worksheet->writeString( $i, $j++, $row['order_id'] );
        $worksheet->writeString( $i, $j++, $row['date_added'] );            
        $worksheet->writeString( $i, $j++, $row['firstname'] );
        $worksheet->writeString( $i, $j++, $row['lastname'] );
        $worksheet->writeString( $i, $j++, $row['payment_method'] );
        $worksheet->writeString( $i, $j++, $row['shipping_firstname'] );
        $worksheet->writeString( $i, $j++, $row['shipping_lastname'] );
        $worksheet->writeString( $i, $j++, $row['shipping_company'] );
        $worksheet->writeString( $i, $j++, $row['shipping_address_1'] );
        $worksheet->writeString( $i, $j++, $row['shipping_address_2'] );
        $worksheet->writeString( $i, $j++, $row['shipping_city'] );
        $worksheet->writeString( $i, $j++, $row['shipping_postcode'] );
        $worksheet->writeString( $i, $j++, $row['shipping_zone'] );
        $worksheet->writeString( $i, $j++, $row['shipping_method'] );
        $worksheet->writeString( $i, $j++, $row['shipping_cost'] );
        $worksheet->writeString( $i, $j++, $row['total'] );
        $worksheet->writeString( $i, $j++, $row['model'] );
        $worksheet->writeString( $i, $j++, $row['name'] );
        $worksheet->writeString( $i, $j++, $row['quantity'] );
        $worksheet->writeString( $i, $j++, $row['price'] );
        $worksheet->writeString( $i, $j++, $row['ptotal'] );
        $worksheet->writeString( $i, $j++, $row['tax'] );
        $worksheet->writeString( $i, $j++, $row['sub_total'] );
        $worksheet->writeString( $i, $j++, $row['store_credit'] );
        $worksheet->writeString( $i, $j++, $row['order_status'] );
        $worksheet->writeString( $i, $j++, $row['store_name'] );

        $this->load->model('catalog/product');
        $product = $this->model_catalog_product->getProduct($row['product_id']);
        $worksheet->writeString( $i, $j++, $product['cost'] );

        $worksheet->writeString( $i, $j++, trim(next(explode('(',$row['coupon_code'])),')') );
        $worksheet->writeString( $i, $j++, $row['coupon_value'] );

        $worksheet->writeString( $i, $j++, trim(next(explode('(',$row['gift_code'])),')') );
        $worksheet->writeString( $i, $j++, $row['gift_value'] );

        $worksheet->writeString( $i, $j++, $row['advanced_coupon_code'] );
        $worksheet->writeString( $i, $j++, $row['ac_amount'] );

        $worksheet->writeString( $i, $j++, $row['sales_promo_code'] );
        $worksheet->writeString( $i, $j++, $row['sales_promo_amount'] );


        // works fine don't need $order_options = array();
        //$order_str = array();
        //$order_options = $this->getOrderOptions($row['order_id'], $row['order_product_id']);
        //foreach($order_options as $order_option){
        //  $order_str[] = $order_option['name'].':'.$order_option['value'];
        //}
        //$worksheet->writeString( $i, $j++, implode('@@@' , $order_str));
        //works fine don't need 

        $i += 1;
        $j = 0;
    }
}

Answer 1

在您的查询中，您可以替换此部分:

(SELECT title FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'advanced_coupon') AS advanced_coupon_code,

到这个

(SELECT GROUP_CONCAT(`title`) FROM " . DB_PREFIX . "order_total ot WHERE ot.order_id = o.order_id AND ot.code = 'advanced_coupon' GROUP BY ot.order_id) AS advanced_coupon_code,

或者，如果您相信我，只需将您的 $query = ". . . "; 字符串替换为我的变体即可:

$query  = "
    SELECT 
        o.*, 
        op.name, 
        op.model, 
        op.order_product_id, 
        op.quantity, 
        op.price, 
        op.total AS ptotal, 
        op.product_id, 
        op.tax, 
        (
            SELECT amount 
            FROM " . DB_PREFIX . "sales_promotion_history 
            WHERE order_id = o.order_id
        ) AS sales_promo_amount, 
        (
            SELECT title 
            FROM " . DB_PREFIX . "order_total ot 
            WHERE ot.order_id = o.order_id AND ot.code = 'sales_promotion'
        ) AS sales_promo_code, 
        (
            SELECT value 
            FROM " . DB_PREFIX . "order_total ott 
            WHERE ott.order_id = o.order_id AND ott.code = 'shipping'
        ) AS shipping_cost, 
        (
            SELECT name 
            FROM " . DB_PREFIX . "order_status os 
            WHERE os.order_status_id = o.order_status_id
        ) AS `order_status`, 
        (
            SELECT ot.value 
            FROM " . DB_PREFIX . "order_total ot 
            WHERE ot.order_id = o.order_id AND ot.code = 'sub_total'
        ) AS sub_total, 
        (
            SELECT ot.value 
            FROM " . DB_PREFIX . "order_total ot 
            WHERE ot.order_id = o.order_id AND ot.code = 'credit'
        ) AS store_credit, 
        (
            SELECT ot.title 
            FROM " . DB_PREFIX . "order_total ot 
            WHERE ot.order_id = o.order_id AND ot.code = 'voucher'
        ) AS gift_code, 
        (
            SELECT ot.text 
            FROM " . DB_PREFIX . "order_total ot 
            WHERE ot.order_id = o.order_id AND ot.code = 'voucher'
        ) AS gift_value, 
        (
            SELECT GROUP_CONCAT(`title`) 
            FROM " . DB_PREFIX . "order_total ot 
            WHERE ot.order_id = o.order_id AND ot.code = 'advanced_coupon' 
            GROUP BY ot.order_id
        ) AS advanced_coupon_code,
        (
            SELECT amount 
            FROM " . DB_PREFIX . "advanced_coupon_history 
            WHERE order_id = o.order_id
        ) AS ac_amount, 
        (
            SELECT ot.title 
            FROM " . DB_PREFIX . "order_total ot 
            WHERE ot.order_id = o.order_id AND ot.code = 'coupon'
        ) AS coupon_code, 
        (
            SELECT ot.text 
            FROM " . DB_PREFIX . "order_total ot 
            WHERE ot.order_id = o.order_id AND ot.code = 'coupon'
        ) AS coupon_value 
    FROM `" . DB_PREFIX . "order` o 
    INNER JOIN `" . DB_PREFIX . "order_product` op ON o.order_id=op.order_id 
    WHERE o.order_id IN ( ".$orderid." ) 
    ORDER BY o.order_id";