在这里遇到了一个我似乎无法弄清楚的问题。我有一个来自对 MS-SQL 数据库的查询的结果集,我需要将其导入到 MySQL 数据库中。不要问为什么,我只是不得不问。下面是我的代码,但是当我运行它时,它对结果没有任何作用。谁能帮我?
<?php
putenv("TDSVER=80");
putenv("FREETDSCONF=/etc/freetds/freetds.conf");
putenv("ODBCSYSINI=/etc/odbcinst.ini");
putenv("ODBCINI=/etc/odbc.ini");
$myServer = "*****";
$username = "*****";
$password = "*****";
$database = "*****";
//connection to the database
$dbhandle = mssql_connect($myServer, $username, $password)
or die("Couldn't connect to SQL Server on $myServer");
//select a database to work with
$selected = mssql_select_db($database, $dbhandle)
or die("Couldn't open database $myDB");
$sql_connection = mysqli_connect("*****", "*****", "*****", "*****");
if (mysqli_connect_errno()) {
echo "Failed to connect to the SQL Database: " . mysqli_connect_error();
}
$query = "DECLARE @Agent NVARCHAR(250) = '%*****%';
WITH Agent AS
(SELECT
COUNT (r.id) as 'count',
p.category_id AS 'category'
FROM
service_req r LEFT JOIN
problem_type p on r.problem_type = p.problem_type
WHERE
r.problem_sub_type = p.problem_sub_type
AND r.problem_type = p.problem_type
AND r.responsibility LIKE @Agent
AND r.insert_time BETWEEN '2017-06-01 01:00:00' AND '2018-06-01 01:00:00'
AND p.category_id BETWEEN '491' AND '1050'
AND r.status IN ('3', '32')
AND r.escalation IN ('0', '1', '2')
GROUP BY
p.category_id,
p.problem_sub_type), CatList AS
(SELECT
COUNT (r.id) as 'count',
p.category_id AS 'category'
FROM
service_req r RIGHT JOIN
problem_type p ON r.problem_type = p.problem_type
GROUP BY p.category_id)
SELECT
COALESCE(a.COUNT, 0) as 'count'
--cl.category
FROM
Agent AS A RIGHT JOIN
CatList AS cl ON a.category = cl.category
";
$result = mssql_query($query);
$numRows = mssql_num_rows($result);
//display the results
while ($row = mssql_fetch_array($result)) {
$query2 = "UPDATE performance_engine_sysaidstat_dino SET points_cat_count = ".($row['count']);
$result1 = mysqli_query($sql_connection,$query2);
}
?>
当我运行它的 MSSQL 部分而不输出到浏览器时,我确实得到了结果,但是在尝试更新 Mysql db 时,我遗漏了一些东西。
更新:
我echo $row['count'] . "<br />";
在这里添加了以下代码行:
while ($row = mssql_fetch_array($result)) {
echo $row['count'] . "<br />";
$query2 = "UPDATE performance_engine_sysaidstat_dino SET points_cat_count = ".($row['count']);
$result1 = mysqli_query($sql_connection,$query2) or die(mysqli_error($sql_connection));
}
并且在运行脚本时,我可以在浏览器中看到我的结果集,但没有任何数据被推送到 MySQL 数据库。
更新 2:
我已经从这里修复了我的语法问题
$query2 = "UPDATE performance_engine_sysaidstat_dino SET points_cat_count = ".($row['count']);
对此:
$query2 = "UPDATE performance_engine_sysaidstat_dino SET points_cat_count = '".($row['count'])."'";
但现在它只用结果集中的第一个值更新我的 MySQL 数据库。MSSQL 查询返回的数据是 521 行数据(不同的值),这个数据集需要按原样更新到新数据库。
更新 3:
这是 MySQL 表的表结构
id points_cat_count points_cat_id points_cat_points points_cat_total
1 0 491 0 0
2 0 492 3 0
3 0 493 0 0
4 0 494 1 0
5 0 495 1 0
6 0 496 1 0
7 0 497 3 0
只需添加一个WHERE
子句来UPDATE
查询,因为现在每个循环都会更新所有行。具体来说,使用相关字段,即两个数据库表之间的类别 ID。
此外,使用准备好的语句来参数化查询。注意准备好的语句位于循环外,只有值在循环中迭代绑定:
$query2 = "UPDATE performance_engine_sysaidstat_dino
SET points_cat_count = ? WHERE points_cat_id = ?";
while ($row = mssql_fetch_array($result)) {
echo $row['count'] . "<br />";
$stmt = $mysqli->prepare(query2);
// ASSUMES COUNT IS STR AND CATEGORY IS INT
$stmt->bind_param('si', $row['count'], $row['category'])
$stmt->execute();
}
$stmt->close();
$mysqli->close();
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句