mysql copy table in server to local server with 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?

Silly point: But why are you running the queries you built on $server I thought you wanted to insert this data into $local — RiggsFolly
– RiggsFolly, Commented May 11, 2017 at 11:04

RiggsFolly · Accepted Answer · 2017-05-11 11:07:27Z

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

Collectives™ on Stack Overflow

mysql copy table in server to local server with PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related