PHP实现CSV行订单号与上一行对比并按需留空字段

问题 #

在PrestaShop站点中通过SQL查询生成每日客户订单CSV文件，每行对应订单中的一件商品。目前CSV生成功能正常，但需实现：当当前行的id_order与上一行订单号相同时，部分字段留空以标识同属一个订单。现有PHP代码如下，需添加订单号对比逻辑实现该需求。

<?php
require_once 'dbconfig.php';
try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    $sql = 'SELECT o.id_order ,c.id_customer, c.firstname, c.lastname, a.address1, a.address2, a.postcode, a.city,a.phone,c.email,a.phone_mobile, od.product_id,
     od.product_name,od.product_quantity, od.product_price,o.total_paid_tax_incl, c.id_customer, op.date_add, op.amount, op.payment_method 
    FROM mod582_orders o 
    INNER JOIN mod582_customer c ON o.id_customer = c.id_customer 
    INNER JOIN mod582_address a ON o.id_address_delivery = a.id_address 
    INNER JOIN mod582_order_detail od ON o.id_order = od.id_order 
    INNER JOIN mod582_order_payment op ON o.reference = op.order_reference 
    WHERE CAST(o.date_add AS DATE) LIKE "2023-01%" /*CAST( curdate() AS DATE)*/; 
    ';

    $r = $pdo->query($sql);
$tab = [];
$tab[] = ['ORDNOORDER', 'ORDREFCUSORDER', 'ORDNOCOSTNUMBER','ORDNOCUSTOMER','ORDCUSTOMERCODE','ORDCUSCAT','ORDTYPE','ORDCURRENCY','ORDCURRENCYRATE','ORDDESIGNATION',
'ORDREPCODE','ORDPORT','ORDPORTTYPE','ORDPORTRATE','DEONOORDER','DEOCOMMENT','DEOCOUNTRY','DEONAME','DEOFIRSTNAME','DEOADDRESS1','DEOADDRESS2','DEOZIPCODE','DEOCITY',
'DEOPHONE','DEOMAIL','DEOPHONEPORTABLE','ODLNOORDER','ODLNOORDERLINE','ODLNOARTICLE','ODLARTDESIGN','ODLQUANTITYORDER','ODLTTCCURUPRICE','ODLCODEPARCELLEFLEU',
'PAYNUMPAYMENT','PAYNOCUSTOMER','PAYNOORDER','PAYNOCURRENCY','PAYDATEPAYMENT','PAYPAYMENTTTCCUR','PAYCURRENCYRATE','PAYCONTREPARTIE'];
$odrline = 1;
while($rs = $r->fetch(PDO::FETCH_ASSOC)){
$tab[] = [$rs['id_order'], $rs['id_order'], '17', '', 'AAA'.$rs['id_customer'],'DET','O','EUR','1','','115','D','P','17', $rs['id_order'],'','FRA', $rs['firstname'], 
$rs['lastname'], $rs['address1'], $rs['address2'], $rs['postcode'], $rs['city'], $rs['phone'], $rs['email'], $rs['phone_mobile'], $rs['id_order'],$odrline, 
$rs['product_id'], $rs['product_name'], $rs['product_quantity'], $rs['product_price'],'','','', $rs['id_order'],'EUR', $rs['date_add'], $rs['amount'],'1','VIR'];
}

$fichier_csv = new SplFileObject('vinistoria/commandes.csv', 'w');
foreach($tab as $ligne){
$fichier_csv->fputcsv($ligne, ';');
}
$date = date('d-m-y h:i:s');
echo "nouveau fichier commandes.csv créé à ". $date;


} catch (PDOException $e) {
    die("Could not connect to the database $dbname :". $e->getMessage());
}
?>

期望效果：当订单号与上一行相同时，部分字段留空以标识同属一个订单。

解决方案 #

修改思路 #

通过记录上一行的订单ID，在循环读取数据时对比当前订单ID与上一行ID：

• 若ID相同，将订单基础信息、客户信息、地址信息、支付信息等重复字段设为空值，仅保留商品行相关字段；
• 若ID不同，正常赋值所有字段，并重置订单行号。

修改后的完整代码 #

<?php
require_once 'dbconfig.php';
try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    $sql = 'SELECT o.id_order ,c.id_customer, c.firstname, c.lastname, a.address1, a.address2, a.postcode, a.city,a.phone,c.email,a.phone_mobile, od.product_id,
     od.product_name,od.product_quantity, od.product_price,o.total_paid_tax_incl, c.id_customer, op.date_add, op.amount, op.payment_method 
    FROM mod582_orders o 
    INNER JOIN mod582_customer c ON o.id_customer = c.id_customer 
    INNER JOIN mod582_address a ON o.id_address_delivery = a.id_address 
    INNER JOIN mod582_order_detail od ON o.id_order = od.id_order 
    INNER JOIN mod582_order_payment op ON o.reference = op.order_reference 
    WHERE CAST(o.date_add AS DATE) LIKE "2023-01%" /*CAST( curdate() AS DATE)*/; 
    ';

    $r = $pdo->query($sql);
    $tab = [];
    $tab[] = ['ORDNOORDER', 'ORDREFCUSORDER', 'ORDNOCOSTNUMBER','ORDNOCUSTOMER','ORDCUSTOMERCODE','ORDCUSCAT','ORDTYPE','ORDCURRENCY','ORDCURRENCYRATE','ORDDESIGNATION',
    'ORDREPCODE','ORDPORT','ORDPORTTYPE','ORDPORTRATE','DEONOORDER','DEOCOMMENT','DEOCOUNTRY','DEONAME','DEOFIRSTNAME','DEOADDRESS1','DEOADDRESS2','DEOZIPCODE','DEOCITY',
    'DEOPHONE','DEOMAIL','DEOPHONEPORTABLE','ODLNOORDER','ODLNOORDERLINE','ODLNOARTICLE','ODLARTDESIGN','ODLQUANTITYORDER','ODLTTCCURUPRICE','ODLCODEPARCELLEFLEU',
    'PAYNUMPAYMENT','PAYNOCUSTOMER','PAYNOORDER','PAYNOCURRENCY','PAYDATEPAYMENT','PAYPAYMENTTTCCUR','PAYCURRENCYRATE','PAYCONTREPARTIE'];
    
    $odrline = 1;
    $previousOrderId = null; // 记录上一行的订单ID

    while($rs = $r->fetch(PDO::FETCH_ASSOC)){
        $currentOrderId = $rs['id_order'];
        
        // 判断当前订单是否与上一行相同
        if ($currentOrderId === $previousOrderId) {
            $odrline++; // 订单行号递增
            // 重复订单字段留空，仅保留商品相关字段
            $lineData = [
                '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
                $currentOrderId, $odrline,
                $rs['product_id'], $rs['product_name'], $rs['product_quantity'], $rs['product_price'], '', '', '',
                '', '', '', '', '', '', '', ''
            ];
        } else {
            $odrline = 1; // 新订单重置行号
            // 正常赋值所有字段
            $lineData = [
                $currentOrderId, $currentOrderId, '17', '', 'AAA'.$rs['id_customer'],'DET','O','EUR','1','','115','D','P','17', 
                $currentOrderId,'','FRA', $rs['firstname'], $rs['lastname'], $rs['address1'], $rs['address2'], $rs['postcode'], 
                $rs['city'], $rs['phone'], $rs['email'], $rs['phone_mobile'],
                $currentOrderId, $odrline,
                $rs['product_id'], $rs['product_name'], $rs['product_quantity'], $rs['product_price'],'','','',
                $currentOrderId,'EUR', $rs['date_add'], $rs['amount'],'1','VIR'
            ];
        }
        
        $tab[] = $lineData;
        $previousOrderId = $currentOrderId; // 更新上一行订单ID
    }

    $fichier_csv = new SplFileObject('vinistoria/commandes.csv', 'w');
    foreach($tab as $ligne){
        $fichier_csv->fputcsv($ligne, ';');
    }
    $date = date('d-m-y h:i:s');
    echo "nouveau fichier commandes.csv créé à ". $date;

} catch (PDOException $e) {
    die("Could not connect to the database $dbname :". $e->getMessage());
}
?>

关键说明 #

• $previousOrderId变量用于存储上一行的订单ID，初始值设为null；
• 每次循环时先获取当前订单ID，与$previousOrderId对比：
  • 相同则仅填充商品行相关字段，其余重复字段留空，同时订单行号$odrline递增；
  • 不同则正常填充所有字段，重置订单行号为1；
• 循环结束后更新$previousOrderId为当前订单ID，供下一次循环对比使用。

内容的提问来源于stack exchange，提问作者Gaiaryde