Update Results from MS-SQL Query into MySql Database

Question

Running into a bit of an issue here that I can't seem to figure it out. I have a result set from a query on a MS-SQL database which I need to import into a MySQL database. Don't ask why, I just have to. Below is my code, but when I run it, it just doesn't do anything with the results. Can anyone help me?

      <?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);




    }

    ?>

When i run the MSSQL portion of it with out put to the browser i do get results but when trying to update the Mysql db i am missing something.

UPDATE:

I have added the following line of code echo $row['count'] . "<br />"; in here:

   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));
 }

and when running the script i can see my result set in the browser but none of the data is being pushed to the MySQL database.

UPDATE 2:

I have fixed my syntax issue here from this

 $query2 = "UPDATE performance_engine_sysaidstat_dino SET points_cat_count = ".($row['count']);

TO THIS:

 $query2 = "UPDATE performance_engine_sysaidstat_dino SET points_cat_count = '".($row['count'])."'"; 

But now it updates my MySQL database with only the first value in the result set. The returned data from the MSSQL query is 521 rows of data (different values) and this dataset needs to be updated as is to the new db.

UPDATE 3:

Here is the table structure for the MySQL table

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

Simply add a WHERE clause to UPDATE query as right now all rows are updated with each loop. Specifically, use the related fields, namely the category ids between both database tables.

Also, use prepared statements to parameterize queries. Notice prepared statement lies outside loop and only values are binded iteratively in loop:

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