0

I'm trying to make a PHP code that allows me to compare and sync differences between two identical tables in SQL Server and MySQL. This makes it so I'll only have to manually update one of them. However, instead of only inserting in the differences, it tries to insert everything from SQL Server (main) to MySQL (sub), doubling up the contents. Here is the code:

<?php

$serverName = "<servername>";
$connectionInfo_mssql = array("Database"=>"<dbname>", "CharacterSet"=>"UTF-8");

try
  {
    $conn_mssql = new PDO("sqlsrv:Server=$serverName;Database=<dbname>");
    $conn_mssql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $conn_mysql = new PDO("mysql:host=localhost;dbname=<dbname>", "<username>", "<password>");
    $conn_mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $mssql_array = array();
    $mssql_query = $conn_mssql->prepare("SELECT * FROM SALARY");
    $mssql_query->execute();

    while ($row = $mssql_query->fetch(PDO::FETCH_BOTH))
    {
      $mssql_array[] = array('SGRADE' => $row['SGRADE'], 'STEP' => $row['STEP'], 'AMOUNT' => $row['AMOUNT'], 'REMARK' => $row['REMARK'], 'uniquee' => $row['uniquee']);
    }

    foreach ($mssql_array as $key => $value)
    {
      $mysql_query = $conn_mysql->prepare("SELECT * FROM SALARY WHERE SGRADE = :SGRADE AND STEP = :STEP AND AMOUNT = :AMOUNT AND REMARK = :REMARK AND uniquee = :uniquee");
      $mysql_query->bindParam(':SGRADE', $value['SGRADE'], PDO::PARAM_INT);
      $mysql_query->bindParam(':STEP', $value['STEP'], PDO::PARAM_INT);
      $mysql_query->bindParam(':AMOUNT', $value['AMOUNT'], PDO::PARAM_INT);
      $mysql_query->bindParam(':REMARK', $value['REMARK'], PDO::PARAM_INT);
      $mysql_query->bindParam(':uniquee', $value['uniquee'], PDO::PARAM_INT);
      $mysql_query->execute();

      if ($mysql_query)
      {
        $mysql_insert = $conn_mysql->prepare("INSERT INTO SALARY VALUES (:SGRADE, :STEP, :AMOUNT, :REMARK, :uniquee)");
        $mysql_insert->bindParam(':SGRADE', $value['SGRADE'], PDO::PARAM_INT);
        $mysql_insert->bindParam(':STEP', $value['STEP'], PDO::PARAM_INT);
        $mysql_insert->bindParam(':AMOUNT', $value['AMOUNT'], PDO::PARAM_INT);
        $mysql_insert->bindParam(':REMARK', $value['REMARK'], PDO::PARAM_INT);
        $mysql_insert->bindParam(':uniquee', $value['uniquee'], PDO::PARAM_INT);
        $mysql_insert->execute();
        echo "Success";
      }
    }
  }
catch(PDOException $e)
  {
    echo "Error: " . $e->getMessage();
  }

?>

How this code should work is that it selects all the contents of the SQL Server "SALARY" table, then select everything in the MySQL "SALARY" table, and then insert in only the differences. However, it instead INSERTs everything into the sub table, doubling up the contents. Since I have my 'uniquee" column set as PRIMARY KEY and auto increment, it doesn't allow that to happen and instead sends me an error "Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'". Any ideas on how I could get this to work? I made a similar code that instead of selecting everything from both tables, it truncates the sub table and inserts everything from the main, which works perfectly but I dislike this method because it becomes very slow when dealing with hundreds of data. Any ideas on how I can get the code above to work?

YMSmash
  • 65
  • 6
  • Possible duplicate of [Trying to sync tables from SQL Server and MySQL; query error when trying to select all data from MySQL with a where statement from an array](https://stackoverflow.com/questions/55796894/trying-to-sync-tables-from-sql-server-and-mysql-query-error-when-trying-to-sele) – Raymond Nijland Apr 23 '19 at 17:14
  • Also SQL Server has [linked servers](https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017) which can [directly query MySQL servers](https://stackoverflow.com/questions/16687999/select-from-linked-mysql-server) – Raymond Nijland Apr 23 '19 at 17:15
  • .. Also take a look into [Distributed Queries Stored Procedures (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/distributed-queries-stored-procedures-transact-sql?view=sql-server-2017) where they explain how you can add a linked server also. – Raymond Nijland Apr 23 '19 at 17:19

0 Answers0