Jump to content

Inserting records a number of times equivalent to column value


thenorman138

Recommended Posts

I have a query where I'm selecting records from DB2 database and storing the values in an array:

 

 


  $orderIdsStr = "'" . implode("', '", $order_ids) . "'";
//SELECT FROM DB2 WITH THE ORDER NUMBERS FIRST
$query = "SELECT invnoc as INVOICE,
                 cstnoc AS DEALER,
                 framec AS FRAME,
                 covr1c AS COVER,
                 colr1c AS COLOR ,
                 extd2d AS SHIPDATE,
                 orqtyc AS QUANTITY
          FROM GPORPCFL
          WHERE invnoc IN ({$orderIdsStr})
          GROUP BY invnoc,cstnoc, slsnoc, orqtyc, framec, covr1c,colr1c, extd2d
          ORDER BY invnoc asc
          LIMIT 35";




$Db2ShipRslt = odbc_exec($DB2Conn, $query);
if ( $Db2ShipRslt === false ) {
     exit (odbc_errormsg($DB2Conn));
}




//Process the results
$Db2ShipArr = array();
while($db2ShipRow = odbc_fetch_array($Db2ShipRslt)){
{
    //Output the record
    print_r($db2ShipRow);    /*This prints the correct Rows*/
    //Append record to results array
    $Db2ShipArr[] = $db2ShipRow;
}

And this works, pulling correct records and storing them properly. 

 

I also have an insert that works properly (it inserts the exact number of records and appropriate values). BUt I'm wondering how I can apply a certain condition to the insert and I've never  had to do this.

 

My select grabs a value called ```orqtyc``` as alias ```QUANTITY``` and I insert those values to my mysql ```placements_new``` table in a column called ```locations```. This is appropriate but I'd also like to be able to have it insert each record  a number of times equal to that value. 

 

Basically ```if row is < :QUANTITY , keep inserting until the record is inserted n times, n being :QUANTITY value```. So if ```:QUANTITY``` = 3, that same record should be inserted 3 times. Technically there would be duplicates but this way each row has it's own set of dates.

 

Again, the select/insert both work and the arrays return what I need. All I need some help with is how to make the insert write the number of rows = to the quantity value.

 

Here's the other part where the insert takes place:

 

 


  foreach($Db2ShipArr as $Db2Ship){


/*THIS IS THE PROBLEM AREA*/


if($rows < :QUANTITY, INSERT n = :QUANTITY){
//INSERT # of records equal to QUANTITY
$stmt = $PDO->prepare("
    INSERT IGNORE 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 
");




$PDO->beginTransaction();
$i = 0;
while($db2row = odbc_fetch_array($Db2ShipRslt)) {




    if(++$i % 1000 == 0) {
        $PDO->commit();
        $PDO->beginTransaction();
    }
    $stmt->execute($db2row);
}
$PDO->commit();
}
}
}
}

 

Here's the fiddle I made. so in this fiddle the record I've inserted for GPORPCFL would, through the php logic/looop, be inserted into placements_new 3 times, since 3 is the value of ```orqtyc```:

 


Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.