基于php脚本中的db2查询的结果插入到mysql表中

Question

I'm trying to modify a script that has a very basic SELECT query running on DB2. I need to store that result set, preferably in an array chunk so I can run it in batches, but in an array nonetheless, and then I need to use those values in an INSERT/SELECT on MYSQL.

I'm thinking, after some feedback, that I might have to use the DB2 results as constants but I don't really know how to go about that.

Here is a mock up of how I am selecting, and how I then need to insert based on the select result set and a subquery on another mysql table:

I'm selecting from here: DB2 Query Results

Invoice | Dealer | Rep | quantity | Frame | Cover | Color | Placements | shipdate
---------------------------------------------------------------------------------
100        123     250     1         1234    12       1         2         20180219
101        321     250     1         1235    12       1         2         20180219
102        432     250     1         1236    12       1         2         20180219

I need to join the former data, in a sense, to the following data in the SKU Table (db2.style = sku.groupID, db2.frame = sku.frame, db2.cover = sku.cover, and db2.color = sku.color) in order to get the correct ID

ID | Frame | GroupID | cover | color 
------------------------------------
15    1234      1        12      1
16    1235      2        12      1
17    1236      3        12      1

Then below, I need to do an insert of the previously determined ID, as well as some of the data from the original DB2 query (insert style into groupID, dealer into dealerID, shipdate into startdate, placements into placements)

INSERT would result in: (skuplacement table)

sku_id | groupID | dealerID | startDate | expirationDate          | placements
------------------------------------------------------------------------------
15          1       123        20180226    (shipdate + 127 days)       2
16          2       123        20180226    (shipdate + 127 days)       2
17          3       123        20180226    (shipdate + 127 days)       2

I know that I can't join between DB2 and MySQL so I need a way to use the values from my select in an array and take those variables to use in the mysql INSERT and Sub-Query. I'm hoping I can do this all within the PHP script and not a CSV/temp table approach.

Here is the sript:

try {
                $DB2Conn = odbc_connect("","", "");

                if(!$DB2Conn){
                  die("Could not connect");
                }else{
                    echo"Connected to DB2";
                }

                $plcQueryDB2 = " 

                        select  invnoc AS INVOICE,
                                cstnoc AS DEALER,
                                slsnoc AS REP,
                                orqtyc AS QUANTITY,
                                framec AS FRAME,
                                covr1c AS COVER,
                                colr1c AS COLOR ,
                                extd1d AS SHIPDATE
                          FROM GPORPCFL
                          group by invnoc,cstnoc, slsnoc, orqtyc, framec, covr1c,colr1c, extd1d
                          limit 200
                ";

                $prep = odbc_prepare($DB2Conn, $plcQueryDB2);
                $exec = odbc_execute($prep);
                $result = odbc_exec($DB2Conn, $plcQueryDB2);
            }
            catch(Exception $e) {  
                echo $e->getMessage();  
            } 

            $array = array();

            //$i = 0;
            while($arr = odbc_fetch_array($result)) 
            {

                // if(++$i % 200 == 0) {

                // }

                $array[] = $arr;
                //echo $arr;

            }




            //Put $result into array chunk, process records in hundreds, possibly by 200 at a time

            //Logic for the query results Insert/ Upsert
            //This is pseudo code
            // if ($row['placements'] < count){   //Here, I am iterating to ensure that there are only as many records inserted as there are placements. 3 placements = 3 records

            // $insertsql = "

            // ";
            // }

I have some pseudo code there after the insert and some logic that isn't quite worked out, but the main thing I need to work out first is how to insert in a way that I can equate my db2 result set to fields in a mysql table, and then take all of those results to put into a third mysql table. Hopefully my mock up at the top makes sense.

I can answer any questions to clarify.

Answer 1

I would do this inside the loop where you fetch rows from DB2.

Assume you have a PDO connection $pdo to your MySQL database.

$stmt = $pdo->prepare("
    INSERT INTO skuplacement (sku_id, groupID, dealerID, startDate, expirationDate, placements)
    SELECT id, groupID, :DEALER, :SHIPDATE, :PLACEMENTS
    FROM sku
    WHERE groupID=:STYLE AND frame=:FRAME AND cover=:COVER AND color=:COLOR
");

$pdo->beginTransaction();
$i = 0;
while($db2row = odbc_fetch_array($result)) {

    if(++$i % 1000 == 0) {
        $pdo->commit();
        $pdo->beginTransaction();
    }

    $stmt->execute($db2row);

}
$pdo->commit();

PDO allows you to pass an associative array to execute(), and the array keys are matched to the named parameter placeholders in the prepared query. But your associative array must have exactly the same set of keys as the parameters.

So you will need to change your DB2 query to return only STYLE, DEALER, FRAME, COVER, COLOR, SHIPDATE, PLACEMENTS.