根据php脚本中db2查询的结果插入mysql表 [英] Inserting into mysql table based on results from a db2 query in php script
问题描述
我正在尝试修改一个脚本，该脚本具有在DB2上运行的非常基本的SELECT查询.我需要存储该结果集，最好将其存储在数组块中，以便可以批量运行它，但是仍然可以在数组中运行，然后需要在MYSQL的INSERT/SELECT中使用这些值.
在考虑了一些反馈之后，我想我可能不得不将DB2结果用作常量，但是我真的不知道该怎么做.
这里是我如何选择以及如何根据选择结果集和另一个mysql表上的子查询进行插入的模型:
我从这里选择:DB2查询结果
发票|经销商|代表|数量框架|封面颜色|展示位置|发货日期---------------------------------------------------------------------------------100123250 1 1234 12 1 2 20180219101321250 1 1235 12 1 2 20180219102432250 1 1236 12 1 2 20180219 从某种意义上说，我需要将以前的数据与后面的数据结合起来SKU表(db2.style = sku.groupID，db2.frame = sku.frame，db2.cover = sku.cover和db2.color = sku.color)以获取正确的ID
ID |框架|GroupID |封面|颜色------------------------------------15 1234 1 12 116 1235 2 12 117 1236 3 12 1 然后，在下面，我需要插入先前确定的ID以及原始DB2查询中的一些数据(将样式插入groupID，将交易者插入DealerID，将发运日期插入开始日期，将展示位置插入展示位置)
INSERT将导致:(skuplacement表)
sku_id |groupID |DealerID |startDate |expirationDate |刊登位置------------------------------------------------------------------------------15 1 123 20180226(发货日期+ 127天)216 2 123 20180226(发货日期+ 127天)217 3 123 20180226(发货日期+ 127天)2 我知道我不能在DB2和MySQL之间加入，所以我需要一种方法来使用数组中select的值并将这些变量用于mysql INSERT和Sub-Query.我希望我可以在PHP脚本中完成所有操作，而不是CSV/临时表方法.
这是便笺簿:
try {$ DB2Conn = odbc_connect("，"，");if(！$ DB2Conn){die(无法连接");}别的{回声已连接到DB2"；}$ plcQueryDB2 =选择invnoc AS INVOICE，cstnoc作为经销商，slsnoc AS REP，orqtyc作为数量，framec AS FRAME，covr1c作为封面，colr1c AS颜色，extd1d为SHIPDATE来自GPORPCFL按invnoc，cstnoc，slsnoc，orqtyc，framec，covr1c，colr1c，extd1d分组限制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;}//将$ result放入数组块中，处理数百条记录，一次可处理200条//查询结果的逻辑Insert/Upsert//这是伪代码//if($ row ['placements']< count){//在此，我要确保插入的记录数与放置数一样多.3个展示位置= 3个记录//$ insertsql =//;//} 在插入之后，我有一些伪代码，还有一些尚无法解决的逻辑，但是我首先需要解决的主要问题是如何以一种可以将db2结果集等同于字段的方式进行插入在mysql表中，然后将所有这些结果放入第三个mysql表中.希望我在顶部进行模拟是有道理的.
我可以回答任何要澄清的问题.
我将在您从DB2提取行的循环内执行此操作.
假设您与MySQL数据库建立了PDO连接 $ pdo .
$ stmt = $ pdo-> prepare(插入skuplacement(sku_id，groupID，dealerID，startDate，expirationDate，展示位置)SELECT ID，GroupID，:DEALER，:SHIPDATE，:PLACEMENTS从sku其中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允许您将关联数组传递给 execute()，并且数组键与准备好的查询中的命名参数占位符匹配.但是您的关联数组必须具有与参数完全相同的键集.
因此，您将需要更改DB2查询以仅返回样式，经销商，框架，封面，颜色，运费，位置.
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 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.
这篇关于根据php脚本中db2查询的结果插入mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
