php - PDO多次插入记录，等于数据库值

Question

我有一个脚本，可以选择数据、创建数组和插入数据(全部使用 PDO)。我遇到的唯一问题是我需要修改插入和更新的方式。

很简单，每条不同的记录都需要以重复的方式插入(这听起来很疯狂，我已经向我的团队领导提出了这个问题，但共识是我们希望每条记录都有自己的日期字段，因为只有一些将会被更新)。听起来应该没那么难，但我从来没有做过不使用 UPDATE ON DUPLICATE KEY 或 INSERT IGNORE 的事情。这实际上是在寻找复制某些记录。

基本上这完全取决于我第一个选择中的 orqtyc AS QUANTITY 字段。

我需要构建几个 if 语句，但主要问题是我首先需要说类似```WHILE insert count is < :QUANTITY, keep inserting until number of inserts equals :QUANTITY

这有意义吗？如果没有，我可以澄清一下。

基本上我需要帮助来做一些逻辑来修改我如何执行这个已经工作的插入。我可以创建我需要的 if/else 语句，但这是我需要的第一件事，因为将要完成的任何插入或更新都必须等于该订单的数量字段。

所以基本上对于每条插入的记录，应该插入 'n' 条记录。如果记录1的数量为1，则只插入1。如果记录2的数量为10，则插入该记录10次。

$order_ids = [];
while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
$order_ids[] = $row['order_id'];
}

if (count($order_ids) > 0) {
$placeholders = implode(',', array_fill(0, count($order_ids), '?'));
$detailStatCheck = "
    SELECT 
         invnoc as INVOICE,
         fstatc as STATUS,
         cstnoc AS DEALER,
         framec AS FRAME,
         covr1c AS COVER,
         colr1c AS COLOR ,
         extd2d AS SHIPDATE,
         orqtyc AS QUANTITY
    FROM GPORPCFL
    WHERE invnoc IN ($placeholders)
";

try {
    $detailCheck = $DB2conn->prepare($detailStatCheck);
    $detailRslt = $detailCheck->execute($order_ids);
    $count2 = $detailCheck->fetch();
    print_r($order_ids);
    print_r($count2);
} catch(PDOException $ex) {
    echo "QUERY FAILED!: " .$ex->getMessage();
}


while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)){


 //IF exists and today is before expire date
 //update records = quantity, or insert

 //ELSEIF exists and today is  past expire_date, just insert number for each quantity

 //ELSE doesn't exist at all, perform below

$values = [
 ":DEALER" => $row2["DEALER"],
 ":SHIPDATE" => $row2["SHIPDATE"],
 ":QUANTITY" => $row2["QUANTITY"],
 ":INVOICE" => $row2["INVOICE"],
 ":FRAME" => $row2["FRAME"],
 ":COVER" => $row2["COVER"],
 ":COLOR" => $row2["COLOR"],
];


 $insertPlacement = "
 INSERT INTO placements_new (sku_id, group_id, dealer_id, start_date, expire_date, locations, order_num)
 SELECT 
  id, 
  sku_group_id, 
  :DEALER, 
  DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 7 DAY) as start_date,
  DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY) as expire_date, 
  :QUANTITY,
  :INVOICE  
FROM skus s  
  WHERE  s.frame=:FRAME AND s.cover1=:COVER AND s.color1=:COLOR
 ";
 try{
 $insert = $MysqlConn->prepare($insertPlacement);
 $insertRslt = $insert->execute($values);
 }catch(PDOException $ex){
 echo "QUERY FAILED!!!: " . $ex->getMessage();
 }
 }
}

Answer 1

所以，我想太多了，为 QUANTITY 值添加一个迭代器是最简单的方法

 while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) {

    //IF exists and today is before expire date
    //update records = quantity, or insert
    //ELSEIF exists and today is  past expire_date, just insert number for each quantity
    //ELSE doesn't exist at all, perform below

    $values = [
        ":DEALER" => $row2["DEALER"],
        ":SHIPDATE" => $row2["SHIPDATE"],
        ":QUANTITY" => $row2["QUANTITY"],
        ":INVOICE" => $row2["INVOICE"],
        ":FRAME" => $row2["FRAME"],
        ":COVER" => $row2["COVER"],
        ":COLOR" => $row2["COLOR"],
    ];

    for($i=0; $i<$row2["QUANTITY"]; $i++) {

        try{
            $insert = $MysqlConn->prepare($insertPlacement);
            $insertRslt = $insert->execute($values);
        }catch(PDOException $ex){
            echo "QUERY FAILED!!!: " . $ex->getMessage();
        }

    }
}