php - DB2 根据 php 脚本中的绑定参数选择和插入
问题描述
我一直在尝试慢慢迁移一个选择数据的脚本,并根据绑定参数将其插入到另一个表中,从 mysql 到 db2。
我已经迁移了大部分内容,但是这个主要部分仍然无法插入。我的选择正在工作,完全符合我的期望。但是,在我创建数组或参数值的地方出了点问题。我只是试图遍历选定的值行并将值插入到匹配的表中。
我是在错误地使用 odbc_fetch_array,还是我的绑定参数看起来有问题?
//Main query to select data
$data = "
SELECT
u.extension
, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound
from SESSION a
join call_summary b
on a.notablecallid = b.notablecallid
inner join system_USERS u
on u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
group by extension,u.user_id" or die(db2_conn_error($DB2Conn));
$stmt = "
INSERT into daily_call_totals
(extension,
total_talk_time_seconds,
total_talk_time_minutes,
total_outbound)
VALUES (?, ?, ?, ?)" or die(db2_conn_error($DB2Conn));
//create array for binding
$content = [];
$mainResult = odbc_exec($DB2Conn, $data);
while ($d = odbc_fetch_array($mainResult)) {
$prepInsert = odbc_prepare($DB2Conn, $stmt);
//for each row, bind param. This is to ensure we get the correct number of records whether they're being inserted or updated for duplicates
$values = [
$d['extension'],
$d['total_talk_time_seconds'],
$d['total_talk_time_minutes'],
$d['total_outbound']];
// Store the current row
$content[] = $d;
if($prepInsert){
$result = odbc_execute($prepInsert,$values);
if($result){
print "successfully added record";
}
}
}
解决方案
如前所述,考虑INSERT...SELECT
并避免循环INSERT...VALUES
。事实上,甚至考虑使用该NOT EXISTS
子句避免重复的 ANSI 版本(即,在所有 SQL 支持的 DBMS 中兼容)。但这只会运行一个动作,而不是有条件地运行两个动作。
下面可以随时运行,并且只会附加date_of_report和extension的唯一配对,而忽略匹配项。请务必将date_of_report和my_report_date_column替换为实际列,因为您从未在聚合查询中明确这样做。
INSERT into daily_call_totals
(date_of_report, -- UPDATE COLUMN
extension,
total_talk_time_seconds,
total_talk_time_minutes,
total_outbound)
SELECT
my_report_date_column, -- UPDATE COLUMN
u.extension
, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound
FROM SESSION a
JOIN call_summary b
ON a.notablecallid = b.notablecallid
JOIN system_USERS u
ON u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
WHERE NOT EXISTS
(SELECT sub.date_of_report, sub.extension
FROM daily_call_totals sub
WHERE sub.date_of_report = my_report_date_column -- UPDATE COLUMN
AND sub.extension = u.extension)
GROUP BY my_report_date_column, extension, u.user_id -- UPDATE COLUMN
现在,如果您想有条件地运行两个操作:1) 更新现有值或 2) 插入新值,然后使用 DB2 MERGE
(在某些但不是所有 RDBMS 中可用):
MERGE INTO daily_call_totals AS d
USING
(SELECT
my_report_date_column, -- UPDATE COLUMN
u.extension
, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound
FROM SESSION a
JOIN call_summary b
ON a.notablecallid = b.notablecallid
JOIN system_USERS u
ON u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
GROUP BY my_report_date_column, extension, u.user_id -- UPDATE COLUMN
) AS q
ON (d.date_of_report = q.my_report_date_column -- UPDATE COLUMN
AND d.extension = q.extension)
WHEN MATCHED THEN
UPDATE SET d.total_talk_time_seconds = q.total_talk_time_seconds,
d.total_talk_time_minutes = q.total_talk_time_minutes,
d.total_outbound = q.total_outbound
WHEN NOT MATCHED THEN
INSERT (date_of_report, -- UPDATE COLUMN
extension,
total_talk_time_seconds,
total_talk_time_minutes,
total_outbound)
VALUES (q.my_report_date_column, -- UPDATE COLUMN
q.extension
q.total_talk_time_seconds
q.total_talk_time_minutes
q.total_outbound);
推荐阅读
- javascript - 根据javascript中的条件修改现有数组对象
- proxy - react-scripts 代理 http/https 服务器正在加载 web 作为响应
- javascript - 我对 javascript 嵌套的 Promise 有一些问题
- node.js - 将来自 gmail 附件的文件存储在 Firebase 存储中
- python-3.x - 如何使用脚本获取文件
- javascript - props 未定义,尽管状态已更改
- php - 如何从 number_format 函数中删除 PHP 中的美分部分
- google-cloud-platform - JanusGraph 大规模数据摄取
- c# - 为什么有些 NuGet 包 dll 被复制到解决方案的输出目录而有些没有?
- linux - 如何以这样的方式组织 bash 文件,以便我可以使用文件夹来获得更好的结构,以便在“导入...路径”依赖项中使用?