Jump to content

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


Go to solution Solved by Psycho,

Recommended Posts

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();
     }
   }
}
Link to post
Share on other sites

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

Link to post
Share on other sites
  • Solution

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();
            }
 
        }
    }
}
Link to post
Share on other sites

@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

Link to post
Share on other sites

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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.