Inserting into mysql table based on results from a db2 query in php script

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.

I deleted that other question, this is modified to reflect recent changes — Geoff_S
– Geoff_S, Commented Feb 26, 2018 at 15:48
a simple way is to retrieve the results from db 1 using valuable and then insert the same values to db 2 , using a button. like in a form. — user7182340
– user7182340, Commented Feb 26, 2018 at 15:59
I'm not sure how that would work here? There's no interface, this is a php script that runs every day to take legacy data from DB2, equate some of those values to a table in mysql, and then take a result set from that and insert into a final mysql table — Geoff_S
– Geoff_S, Commented Feb 26, 2018 at 16:00
You want to re-invent a wheel? Have you seen dbconvert.com/db2/mysql ? Do you know that Db2 (if licensed with Infosphere Federation Server license) can join after-configuration to mysql (via jdbc wrapper, or odbc wrapper) ? — mao
– mao, Commented Feb 26, 2018 at 16:02
@mao I've never seen that before, we don't have that running on our production server but I can look into it. I was just under the impression there may be a fairly simple way to achieve this in the php script. It wouldn't be such a big deal if I were just selecting and inserting but I have to equate some of those DB2 values to mysql values — Geoff_S
– Geoff_S, Commented Feb 26, 2018 at 16:04

Bill Karwin · Accepted Answer · 2018-02-26 16:23:04Z

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.

answered Feb 26, 2018 at 16:23

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Geoff_S Over a year ago

Oh I see, because the invoice date is not necessary obviously. So this takes the stored DB2 variables and this also helps with the array chunk issue. Thank you, I'm about to integrate this into my script and play with it for a bit!

Geoff_S Over a year ago

Quick question @bill Karwin, I have a normal MySQL connection called $conn. WOuld I have to change that to be PDO?

Bill Karwin Over a year ago

I assume you mean $conn is a mysql or mysqli connection (it could be either, I can't tell from your statement). I recommend everyone switch to PDO, it's the only PHP database extension that supports passing the array to execute() as I showed.

Geoff_S Over a year ago

yes it's mysqli, sorry. Ok, I will create a PDO connection then, thanks!

Geoff_S Over a year ago

can I ask exactly what this line is doing: if(++$i % 1000 == 0) { $pdo->commit(); $pdo->beginTransaction(); }

|

Collectives™ on Stack Overflow

Inserting into mysql table based on results from a db2 query in php script

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related