dongsi7759 2018-02-26 15:43 采纳率: 100%
浏览 78
已采纳

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

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.

  • 写回答

1条回答 默认 最新

  • dongzheng4556 2018-02-26 16:23
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 C++/QT设置函数调用条件(多个功能调用同一,且各自调用条件不同)
  • ¥15 UE5样条线生成的网格UV问题
  • ¥15 如何用最短的时间大致看懂springboot+vue的项目
  • ¥15 (有偿)懂数值分析和含时变参数微分方程的来
  • ¥15 layui父页的数据表格如何用弹窗页提交后的查询数据来更新数据表格内容?
  • ¥15 abaqus随机生成二维颗粒
  • ¥15 安装ansys许可证管理器时出现了这个问题,如何解决?
  • ¥100 高价求算法,利用智能手机传感器计算车辆的三轴g值
  • ¥15 Blazor server 数据库操作异常,如何解决?(语言-c#)
  • ¥15 uni-app开发APP运行到浏览器访问接口跨域