具有两个输出到数组的多表mysql/php查询

Question

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

这段代码是opencart xml vqmod的一部分，它将从选定订单中的订单数据导出到excel电子表格。在大多数情况下，它工作得很好。有一个问题，但是，如果有一个记录与一个以上的advanced_coupon列出了一个特定的订单，它将失败。

我确信我需要为输出添加一个数组，它是贴现金额的相关字段，但我不知道如何做到这一点。我对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";

Answer 2

试着替换：

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

通过以下方式：

 foreach( $row['advanced_coupon_code'] as $code )
 {
   $worksheet->writeString( $i, $j++, $code );
 }