服务器中的mysql复制表到PHP的本地服务器

Question

I want to copy a table data in a server to local server with PHP.

This is the script I have

<?php

$h_local = "localhost";
$u_local = "root";
$p_local = "";
$db_local = "";


$h_server = "localhost";
$u_server = "root";
$p_server = "";
$db_server = "";

$server = new mysqli($h_server,$u_server,$p_server,$db_server);

$local = new mysqli($h_local,$u_local,$p_local,$db_local);

$result = $local->query("SELECT * FROM b_barang");

while ($row = $result->fetch_assoc()) {
  foreach ($row as $field => $value) {
    $fields .= "$field, ";
    $values .= "'$value', ";
  }
  // remove trailing ", " from $fields and $values
  $fields = preg_replace('/, $/', '', $fields);
  $values = preg_replace('/, $/', '', $values);

  $sql = "INSERT INTO b_barang ($fields) VALUES ($values)";
  $arr[] = $sql;

}


foreach($arr as $a){
    $server->query($a);
    echo $a."<br><br>";
}
?>

The result is in the first array like

FIRST RESULT

INSERT INTO b_barang (Kode, Nama, Satuan, HP, PPN, Diskon, HJ, Kemasan, Margin, Saldo, TglPesan, Batmin, Batmak, HPAwal, PPNAwal, DiskonAwal, HJAwal, IsiKemasan, HPKemasan, HPKemAwal, HPPAverage, HPPAverageAwal, Jenis, HPP, HPPAwal, SaldoTemp, SaldoInput, Supplier) VALUES ('3', 'barang 3', '', '', '', '', '', '', '', '', '', '', '', '', '0', '', '0', '0', '0', '0', '', '', '', '', '', '0', '0', '')

and the next result is wrong like adding values from first time result

`SECOND RESULT`

INSERT INTO b_barang (Kode, Nama, Satuan, HP, PPN, Diskon, HJ, Kemasan, Margin, Saldo, TglPesan, Batmin, Batmak, HPAwal, PPNAwal, DiskonAwal, HJAwal, IsiKemasan, HPKemasan, HPKemAwal, HPPAverage, HPPAverageAwal, Jenis, HPP, HPPAwal, SaldoTemp, SaldoInput, SupplierKode, Nama, Satuan, HP, PPN, Diskon, HJ, Kemasan, Margin, Saldo, TglPesan, Batmin, Batmak, HPAwal, PPNAwal, DiskonAwal, HJAwal, IsiKemasan, HPKemasan, HPKemAwal, HPPAverage, HPPAverageAwal, Jenis, HPP, HPPAwal, SaldoTemp, SaldoInput, Supplier) VALUES ('3', 'barang 3', '', '', '', '', '', '', '', '', '', '', '', '', '0', '', '0', '0', '0', '0', '', '', '', '', '', '0', '0', '''1', 'barang1', '', '', '', '', '', '', '', '', '', '', '', '', '0', '', '0', '0', '0', '0', '', '', '', '', '', '0', '0', '')

as you can see the values in result 2 adding values from result 1.

how can I solved it?

Answer 1

All you need to do is clear out the $fields and $values variables each time round your while loop. Currently you are adding the second query to the first and then the third query to the first and second.. etc

while ($row = $result->fetch_assoc()) {

    // clear out these 2 variables before starting another iteration
    $fields = '';
    $values = '';

  foreach ($row as $field => $value) {
    $fields .= "$field, ";
    $values .= "'$value', ";
  }
  // remove trailing ", " from $fields and $values
  $fields = preg_replace('/, $/', '', $fields);
  $values = preg_replace('/, $/', '', $values);

  $sql = "INSERT INTO b_barang ($fields) VALUES ($values)";
  $arr[] = $sql;

}

You may also want to check this loop as it appears to be applying the INSERTS to the Server and not the Local database

foreach($arr as $a){
    // Should this line
    //$server->query($a);
    // be
    $local->query($a);
    echo $a."<br><br>";
}