php - 将 MS-SQL 查询的结果更新到 MySql 数据库

Question

这里遇到了一个我似乎无法弄清楚的问题。我有一个查询 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 数据库时，我遗漏了一些东西。

更新:

我添加了以下代码行 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

Answer 1

只需将 WHERE 子句添加到 UPDATE 查询，因为现在每个循环都会更新所有行。具体来说，使用相关字段，即两个数据库表之间的类别 ID。

此外，使用 prepared statements参数化查询。注意准备好的语句位于循环之外，只有值是 binded循环迭代:

$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();