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

Debugging hint: mysqli_query($sql_connection,$query2) or die(mysqli_error($sql_connection))...... you know that the update query UPDATE performance_engine_sysaidstat_dino SET points_cat_count = ".($row['count'] UPDATE all rows in the table are you sure this is what you need? — Raymond Nijland
– Raymond Nijland, Commented Jun 8, 2017 at 14:24
@RaymondNijland yes i get a result set of 521 rows which i need to push to another database. these values are then used as base of a different formula on that database — Peter Strauss
– Peter Strauss, Commented Jun 8, 2017 at 14:33
Problem is, even with debugging there are no error message whatsoever. — Peter Strauss
– Peter Strauss, Commented Jun 8, 2017 at 14:36
@RaymondNijland added some updates to my original post. Maybe you seeing something that i am missing? — Peter Strauss
– Peter Strauss, Commented Jun 8, 2017 at 15:07
Are you sure only the first value is updated to MySQL or the last? You include no WHERE clause in UPDATE, so table field is constantly updated with each loop ending with last. What is your end goal? All N=521 rows as a column in table? — Parfait
– Parfait, Commented Jun 8, 2017 at 15:38

Parfait · Accepted Answer · 2017-06-08 16:08:03Z

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

answered Jun 8, 2017 at 16:08

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Strauss Over a year ago

Thanks a mill! changed the variables to match mine and it is working now!!

Collectives™ on Stack Overflow

Update Results from MS-SQL Query into MySql Database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related