Looping so that each record is inserted a number of times equal to a column value

[thenorman138]

I have a script that is working as far as selecting data, creating an array and inserting data (all using PDO). The only issue I'm having is that I need to modify how I do my inserts and updates.

 

Quite simply, each distinct record needs to be inserted in duplicate fashion (this sounds crazy, and I've brought that up to my team leader but the consensus is that we want every record to have their own date fields because only some will be updated). It sounds like it shouldn't be so difficult but I've never done something where I didn't use UPDATE ON DUPLICATE KEY or INSERT IGNORE. This is literally looking to duplicate certain records.

 

Basically this is all dependent on the ```orqtyc AS QUANTITY``` field in my first select.

 

I need to build several if statements, but the main issue is that  I first need to say something like ```WHILE insert count is < :QUANTITY, keep inserting until number of inserts equals :QUANTITY

 

Does that make sense? If not, I can clarify. 

 

Basically I need help doing some logic to modify how I perform this already working insert. I can create the if/else statements I need but this is the first thing I need because any INSERT or UPDATE that's going to be done has to be equal to the quantity field for that order.

 

So basically for every record inserted, there should be 'n' number of records inserted. If record number 1 has quantity of 1, only insert 1. If record 2 has quantity of 10, insert that record 10 times.

$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();
     }
   }
}

[Barand]

I have closed your previous post on this topic.

 

Do not post multiple versions of the same problem.

[thenorman138]

@Barand sorry about that, I meant to delete it since this is a much more updated version of that. I overlooked that and shouldn't have left it open. I'll keep an eye on that in the future!

[ginerjm]

Are all these duplicate records the same? It is puzzling why one would design something that required all these copies of the same data.

[thenorman138]

They're not the same. It's kind of confusing but it goes along with some of our business logic. When someone makes an order, it's usually for a quantity of 1, meaning they're putting 1 of that piece of furniture in their store. However, some people have multiple storefronts. This is a fairly temporary solution to keeping track and getting a more accurate count of stores. I'd say about 6% of them have more than one store so this is showing that they are ordering a piece of furniture for each store and we need a record for each. So no they are not all the same, and most of them will be one record a piece anyway

[Psycho]

You only need to prepare a query ONCE - then run it multiple times as needed (with different data). You are preparing the same query multiple times.

 

 

$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();
    }
 
    //Create prepared INSERT query
    $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
       ";
 
    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();
            }
 
        }
    }
}

[thenorman138]

@psycho thank you, and I had actually taken your previous advice on preparing queries, as well as using PDO across the board, but I had moved everything back in the loop because I was testing something with one of my arrays and an exit, but I should have gotten things back in place before I posted it here. However, thank you for the answer and rearrangement. That seems to work exactly as I expected! Now I can apply my IF/ELSE statements before hand to determine if it needs to be inserted or not. YOu don't see a problem with where I have commented that I will created those do you? Basically, do the conditions and then if they're true then I'll encapsulate the for statement to insert

[Psycho]

I really don't understand your supposed logic. To be honest, the whole things seems backwards. I have a pretty confident feeling that this is being made way more complicated than it should be.