Inserting records if there's no match in the database

[thenorman138]

This script is finally returning the arrays and values I need, but the last portion is not quite performing properly. This starts at the if statement that performs an INSERT.

 

If I clear the database table out, it inserts all the originally obtained records ($Db2ShipRslt) as it should. However, say I delete 10 records from the database and run it again. It doesn't insert those 10 missing records like it should.

 

The script is querying the placements table to see if there exists any records with the dealer_id and sku_id related to those DB2 records. The array printing shows that it finds the right matches but my if statement should be saying (If there are no records in placements with the sku_id and dealer_id relating to $Db2ShipRslt records, then insert them. If they match, then ignore. I'll be doing an update with this eventually, ignoring for now. ).

 

So again, I'm returning what I need to here, but the whole point of the last block is to insert any of those DB2 results that don't have a match for the sku_id and dealer_id.

 

 

One other thing I'd like to apply is within the if statement, I want to insert a number of records for the number in the locations (or :QUANTITY) column. So if that column returns '2' which is inserted into 'locations', I'd like to insert that record twice. It's redundant and the same information, but I'd like to have a row count of each record that equates to that location but I'm not sure the best way to do so

 

$orderShippedCheck = "

SELECT 

order_id,

order_status

FROM jfi_sales.order_status

WHERE order_status = 'S'

";



$result = mysqli_query($mysqlConn, $orderShippedCheck);

$order_ids = array();



//loop results to gather order IDs and store them

while ($row = mysqli_fetch_array($result)){

    $order_ids[] = $row['order_id'];

}


print_r($order_ids);   /*This prints the correct order IDs*/


$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";



$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;

}


foreach($Db2ShipArr as $Db2Ship){


//Check to see if there are any records in jfi_sales.placements for the dealer/sku combo

$dealerSkuCheck = "

SELECT 

sku_id,

dealer_id

FROM placements p

INNER JOIN skus s

ON p.sku_id = s.id

WHERE p.dealer_id = '{$Db2Ship['DEALER']}'

AND   s.frame = '{$Db2Ship['FRAME']}'

AND   s.cover1 = '{$Db2Ship['COVER']}'

AND   s.color1 = '{$Db2Ship['COLOR']}'

AND   p.order_num = '{$Db2Ship['INVOICE']}'

";


$existingCheckRslt = mysqli_query($mysqlConn, $dealerSkuCheck);

$existingRecords = array();


while ($existingRow = mysqli_fetch_array($existingCheckRslt, MYSQLI_ASSOC)){

    $existingRecords[] = $existingRow;

}


print_r($existingRecords); /*This prints the right records showing that there exists record(s) in placements that have that dealer_id and sku_id*/



/*THIS IS THE PROBLEM AREA*/


if(mysqli_num_rows($existingCheckRslt) == 0){

//INSERT # of records equal to QUANTITY

$stmt = $PDO->prepare("

    INSERT IGNORE INTO placements (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 jfi.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();

}

}

Edited March 2, 2018 by thenorman138

[Psycho]

I was going to help but, honestly, I'm having a hard time following your code. You are using three different methods to work with the databases mysqli, odbc, and now PDO. While some things may work - the process is not logical/efficient. Indent your code to show logical structure. This is probably your biggest problem. You have loops that are running though much larger record sets than they should be (which was the problem in your other post).

 

You also need to stop running queries in loops - it is a performance killer.

[thenorman138]

@psycho I understand. Maybe I can refactor this a bit and clean it up. Would you suggest moving queries outside of the loops altogether, and then just call them inside?