how to get the while loop three different variable into the json array

Question

actually i try to write the program from get the ms sql data select and then selected data insert to the MySQL table.i write the program which one get the final value of the table. because insert query place out side the while loop.how do i get the all the value in to the insert query??

I think can store in the while loop execution data json array.can i retrieve the one by one?

MSSQL DB connection

<?php
$serverName = "servername"; //serverName\instanceName, 
portNumber (default is 1433)
$connectionInfo = array( "Database"=>"Databasename", 
"UID"=>"username", "PWD"=>"Password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
 /*echo "Connection established.<br />";*/
}else{
 echo "Connection could not be established.<br />";
 die( print_r( sqlsrv_errors(), true));
}
?>

test.php

<?php 
require_once ('inc/MSSQL_connection.php');
//require_once ('inc/MYSQL_connection.php');


     $query ="SELECT EnrolledID,Date,Time FROM dbo.view_attendance
              ORDER BY Date,Time";                    
     $result = sqlsrv_query($conn,$query);
 if ($result == FALSE){
       die(FormatErrors(sqlsrv_errors()));
   }
 else{

      while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))  
     { 

        $emp_no = $row['EnrolledID'];
        $date  = date_format($row['Date'], 'Y-m-d');
        $time = date_format($row['Time'], 'H:i:s');
     }
       //echo $emp_no;
       $conn = mysqli_connect("localhost", "root", "","kelaniya");
       $query = "INSERT INTO attendancedata (EnrolledID,Date,Time) VALUES ('{$emp_no}', '{$date}', '{$time}')";
       mysqli_query($conn, $query); 

  }  
 ?>

Alex Martian · Accepted Answer · 2019-08-29T08:25:57.443

0

IMHO you can just move insert into while loop, why not?:

    <?php 
    require_once ('inc/MSSQL_connection.php');
    //require_once ('inc/MYSQL_connection.php');


    $query ="SELECT EnrolledID,Date,Time FROM dbo.view_attendance
                  ORDER BY Date,Time";                    
    $result = sqlsrv_query($conn,$query);
    if ($result == FALSE){
           die(FormatErrors(sqlsrv_errors()));
    }
    else{
          $conn_insert = mysqli_connect("localhost", "root", "","kelaniya");
          while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))  
           { 

            $emp_no = $row['EnrolledID'];
            $date  = date_format($row['Date'], 'Y-m-d');
            $time = date_format($row['Time'], 'H:i:s');
           //echo $emp_no;

           // MySQLli

           $stmt = $conn_insert->prepare('INSERT INTO attendancedata (EnrolledID,Date,Time) VALUES (?,?,?)');
           // I insert as string as was in original question (table metadata was not given...)
           $stmt->bind_param('sss', $emp_no, $date, $time ); // 's' specifies the variable type => 'string'
           $stmt->execute();
           }
      }  
     ?>

edited Aug 29 '19 at 08:25

answered Aug 29 '19 at 06:52

Alex Martian

3,423
7
36
71

can not do like that .because this not same data base type .one in mssql athoer one is my sql . – Nipun Sachinda Aug 29 '19 at 06:53
it need two kind of database connection in same file. – Nipun Sachinda Aug 29 '19 at 06:54
get the data from mssql table and try to insert into the mysql table – Nipun Sachinda Aug 29 '19 at 06:55
@Nipun Sachinda, I'm not great expert in php, I know SQL to large extent, so assumed php code is correct, and I moved other connection and renamed it to make it work as far as I assumed your code was generally working: `$conn_insert = mysqli_connect("localhost", "root", "","kelaniya");` – Alex Martian Aug 29 '19 at 06:57
1

In this case the mysql connection is `$conn_insert`, so both connections are active in the loop. Although I would also recommend using a prepared statement - https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Nigel Ren Aug 29 '19 at 06:58
can you change answer accordingly? – Nipun Sachinda Aug 29 '19 at 07:04
@Nipun Sachinda, please try updated, I'm not sure if you needed `{}` in your original insert, in examples I saw they are not needed. – Alex Martian Aug 29 '19 at 07:27
@Nipun Sachinda, I used PDO, but it need to be 'initialized' before, I better rewrite – Alex Martian Aug 29 '19 at 07:33
and also need the my sql Connection – Nipun Sachinda Aug 29 '19 at 07:37
@Nipun Sachinda, I rewrote, "and also need the my sql Connection" - it was already there, I used yours – Alex Martian Aug 29 '19 at 07:48
Parse error: syntax error, unexpected 'INTO' (T_STRING), expecting ')' in C:\xampp\htdocs\temp\test.php on line 24 .Get above error bro – Nipun Sachinda Aug 29 '19 at 08:15
@ Nipun Sachinda forgot quotes ' in a hurry - updated – Alex Martian Aug 29 '19 at 08:26
thank you it perfectly work .thank you very much to spend your valuable time for me. – Nipun Sachinda Aug 29 '19 at 08:31
i have send 12M record to send mssql to mysql and mssql database is daliy updated.what is the suitable should i do? – Nipun Sachinda Aug 29 '19 at 08:57
@Nipun Sachinda, I think you can write another question giving more details. I can try to help you there too if you post link as comment to this question. – Alex Martian Aug 29 '19 at 10:23
https://stackoverflow.com/questions/57720402/how-to-check-the-latest-update-data-and-only-update-date-run-in-another-query – Nipun Sachinda Aug 30 '19 at 04:20
can attention above question ?if you have free time – Nipun Sachinda Aug 30 '19 at 04:21

how to get the while loop three different variable into the json array

1 Answers1